How to Read Cell Colors using VBA in Excel (ColorMatch Function)
Question details
The user needs to use VBA to detect the specific fill color of cells to evaluate project scoreboard KPIs (Good, Bad, and Ugly responses).

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building a project scoreboard that relies on the visual status of cells to trigger KPI responses through a VBA custom function.
- Observed behavior
- Users cannot accurately read the cell colors if they are applied via conditional formatting using standard Interior.Color VBA properties.
Ensure your Excel workbook is saved as a Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled to access the VBA Editor.
Use Range.DisplayFormat.Interior.Color for Conditional Formatting
Use this method when your cells are colored automatically via conditional formatting rules, as it captures the actual color rendered on the screen.
The standard Interior.Color property ignores conditional formatting and only reads manual fills. To capture the color dynamically generated by rules, you must use DisplayFormat.
Press ALT + F11 on your keyboard to launch the Visual Basic for Applications editor.
Insert a new Module from the 'Insert' menu and start a new subroutine (Sub).
Use the code snippet `MyColor = Range("A1").DisplayFormat.Interior.Color` to read the displayed color of the target cell.
Compare the captured color code to your target KPI colors (Good, Bad, Ugly) to generate the corresponding scoreboard response.

Use Range.Interior.Color for Manually Filled Cells
If your scoreboard uses manual color fills instead of automated conditional formatting, the standard Interior.Color property is the most straightforward approach.
Alternative: Map Response Text to Scores
For more reliable data processing and scoring, evaluate the underlying data text rather than reading visual color formatting.
Write and Execute VBA Macros Seamlessly in WPS Spreadsheet
WPS Spreadsheet offers robust support for VBA macros, allowing you to automate tasks and run custom functions like reading cell colors. It provides a familiar interface fully compatible with Excel files and VBA scripts.
- 1. Open Your Workbook: Open your macro-enabled spreadsheet (.xlsm) in WPS Spreadsheet.
- 2. Navigate to the Developer Tab: Click on the 'Developer' tab on the top ribbon menu to access macro tools.
- 3. Launch the VBA Editor: Click 'VBA Editor' to start writing or editing your cell color evaluation functions.
- 4. Run the Macro: Execute your macro to seamlessly process your conditional formatting and KPI responses.

Frequently Asked Questions
Why does Interior.Color give the wrong color code?
Interior.Color only detects the background color applied manually by the user. If a cell's color is the result of conditional formatting, Interior.Color will return the default background color of the cell (often clear or no color), completely ignoring the visually displayed conditional format.
Can I use DisplayFormat.Interior.Color inside a worksheet formula?
No, Excel prevents the use of the DisplayFormat property within a User Defined Function (UDF) that is called directly from a cell formula. Attempting to do so will result in an error. You must execute DisplayFormat via a standard Sub macro or an event trigger.
How do I get the color index number instead of the exact color code?
You can use Range.Interior.ColorIndex. This property returns a legacy 56-color palette index number (from 1 to 56), which is sometimes easier to manage than the large numerical RGB values returned by Interior.Color.




