VBA & Macro Problems

How to Count Cells by Conditional Formatting Color Using VBA in Excel

Chanuka Geekiyanage Oct 9, 2026 869 views

Question details

The user needs to count cells based on their conditional formatting color using VBA, but standard worksheet functions return incorrect results or #VALUE! errors.

How to Count Cells by Conditional Formatting Color Using VBA in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to count cells with specific background colors applied via conditional formatting using a VBA User-Defined Function (UDF).
Observed behavior: The VBA function using Interior.ColorIndex fails to recognize conditional formatting colors. Attempting to use DisplayFormat.Interior.ColorIndex in a worksheet formula returns a #VALUE! error.

Before you start

Ensure you have enabled the Developer tab in Excel and saved your workbook as a Macro-Enabled Workbook (.xlsm) before executing VBA code.

Solution 1Recommended

Use a Standard Macro (Sub) with DisplayFormat

Since Excel restricts the DisplayFormat property from working inside User-Defined Functions (UDFs) on a worksheet, use a standard macro to loop through the cells and output the count.

Excel does not allow user-defined worksheet formulas to evaluate the DisplayFormat property. If you try to use it within a function called directly from a cell, Excel will return a #VALUE! error. To properly identify the displayed conditional-formatting color, you must run a standalone macro that evaluates the target range and writes the result directly to your desired cell.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Insert a New Module

Click on 'Insert' in the top menu bar and select 'Module' to create a new blank script window.

Write the Loop Procedure

Create a Sub procedure that loops through your target range. Use 'Range.DisplayFormat.Interior.ColorIndex' to check the visible color of each cell.

Increment the Counter

Add a counter variable in your loop that increases by 1 each time the cell's DisplayFormat color matches your specified criteria.

Output the Result

Set the macro to write the final count variable to a specific output cell (e.g., Range("S1").Value = counter) and run the macro.

Use a Standard Macro (Sub) with DisplayFormat

Understanding DisplayFormat: The DisplayFormat property reads the formatting exactly as it appears on your screen. Regular Interior.Color only reads the static background color and ignores any colors dynamically applied by Conditional Formatting rules.

Advanced Data Management with WPS Office

Run VBA Macros Seamlessly in WPS Spreadsheets

WPS Office Spreadsheets provides robust support for VBA macros, allowing you to automate data processing and manage conditional formatting effortlessly. Write, edit, and execute your macro scripts just as you would in Microsoft Excel.

1. Open Your Workbook: Launch WPS Spreadsheets and open your Macro-Enabled Workbook (.xlsm).
2. Access Developer Tools: Navigate to the 'Developer' tab on the main ribbon interface.
3. Launch VBA Editor: Click the 'VBA Editor' button or press ALT + F11 to access your macro scripts.
4. Execute Your Script: Run your custom Sub procedure to evaluate the conditional formatting and output your counted cells.

High compatibility with Microsoft Excel VBA macros and scriptsNative support for complex conditional formatting evaluationLightweight application with fast data processing speedsFree alternative with a highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA function return #VALUE! when counting colored cells?

If your VBA function uses the DisplayFormat property to evaluate conditional formatting colors, Excel will return a #VALUE! error when that function is typed into a cell as a formula. The DisplayFormat property is restricted and only supported within standard macro Sub procedures, not User-Defined Functions (UDFs).

What is the difference between Interior.Color and DisplayFormat.Interior.Color?

Interior.Color reads the static, manually applied background color of a cell. DisplayFormat.Interior.Color reads the color currently visible on the screen, meaning it can detect colors that are applied dynamically through conditional formatting rules.

Can I automatically update the cell count when the conditional formatting changes?

Because you must use a standard macro rather than a worksheet function to read DisplayFormat, the count will not update automatically in real-time. You will need to manually re-run the macro or trigger it using a worksheet event (like Worksheet_Change or Worksheet_Calculate) to refresh the calculated count.

Excel & Data Problems