Excel VBA: How to Check Whether Cell Colors Match Exactly

VBA & macros · Updated November 19, 2026

Yes. In Excel VBA, compare the cells’ Interior.Color values; they are numeric color values, so an equality test tells you whether their fill colors match. For example, a macro can compare each cell in A2:A100 with the fill in B1 and count the matches, as shown below.

What a VBA color comparison checks

A cell’s fill is part of its formatting, not its value. VBA exposes the fill through the cell’s Interior property. Its Color property returns a numeric value representing the color, which VBA can compare with the equals operator. If two cells return the same value, their fill colors match according to that property.

This is different from comparing the text or number in a cell. A cell containing the word “green” is not necessarily filled green, and two cells with identical values can have different fills. Likewise, a cell’s font color is separate from its fill: use Font.Color to compare text color, rather than Interior.Color.

The example below uses B1 as the reference cell and checks A2:A100. It reports the number of matches without changing the worksheet.

Sub CountMatchingFillColors()
    Dim ws As Worksheet
    Dim checkRange As Range
    Dim cell As Range
    Dim targetColor As Long
    Dim matchCount As Long

    ' Compare ordinary cell fills on the active worksheet.
    Set ws = ActiveSheet
    Set checkRange = ws.Range("A2:A100")
    targetColor = ws.Range("B1").Interior.Color

    For Each cell In checkRange.Cells
        If cell.Interior.Color = targetColor Then
            matchCount = matchCount + 1
        End If
    Next cell

    MsgBox matchCount & " matching cells."
End Sub

For instance, if 12 cells in A2:A100 have the same fill color as B1, the message reports 12. The code compares the fills as they are set directly on the cells; conditional formatting needs an additional consideration.

How to add and run the macro

On Excel for Microsoft 365 for Windows, first save a copy of the workbook. Then open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the code into the module, and close the editor. Run the macro from Developer > Macros, select CountMatchingFillColors, and choose Run. If the Developer tab is not visible, enable it in File > Options > Customize Ribbon.

  • Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm). A standard .xlsx file does not preserve VBA code.
  • Excel may ask whether to enable macros, depending on the file’s source and your security settings. Only enable macros in files you trust.
  • Macros cannot be undone with Ctrl+Z. This particular example only displays a count, but save a copy before running other macros that change cells.

Excel for Mac also supports VBA, though the Visual Basic Editor and macro menus can differ from the Windows instructions; use the Developer tab or the Mac menu commands available in your installation. Excel for the web cannot run VBA macros. To use this code, open the workbook in the desktop Excel app.

Conditional formatting changes which color you see

A common reason for an apparent mismatch is conditional formatting. Interior.Color reads the fill applied directly to the cell. If a conditional-formatting rule changes the cell’s visible fill, that visible result may not be what Interior.Color returns. VBA provides DisplayFormat.Interior.Color to read the formatting as displayed after conditional formatting is applied.

For example, if A2:A100 appears red because of a conditional-formatting rule, compare each cell’s DisplayFormat.Interior.Color with the displayed color of the reference cell. In the earlier macro, replace the two color-reading expressions with the following approach:

targetColor = ws.Range("B1").DisplayFormat.Interior.Color

For Each cell In checkRange.Cells
    If cell.DisplayFormat.Interior.Color = targetColor Then
        matchCount = matchCount + 1
    End If
Next cell

Use this only when the goal is to compare the visible, conditionally formatted fill. The range, target cell, and loop remain the same. A key limitation: DisplayFormat is intended for VBA macros and does not work as a reliable worksheet user-defined function. Also, if the underlying values or rule conditions have not recalculated, the displayed result may not yet reflect the outcome you expect.

If the rule itself expresses the condition you care about—for example, “mark overdue dates red”—it is often more dependable to test the date or status value in VBA than to infer the condition from the resulting color. Formatting is a presentation choice; the rule’s underlying condition is the actual logic.

Color, ColorIndex, themes, and no fill

For most modern Excel workbooks, Interior.Color is the practical choice for comparing fills. Interior.ColorIndex refers to an indexed color value and can be less suitable for theme-based colors and other modern formatting. If the task is to compare the color actually represented by the fill, compare Color.

Excel themes can make color handling look confusing. A fill may be selected as a theme color, while another may be selected as a custom color. If their resulting color values are the same, comparing Interior.Color can treat them as a match even if the original selections were made differently. Conversely, a theme color can look different when the workbook theme changes, so a comparison made before and after a theme change may have a different result.

There is also a difference between a cell with no fill and a cell filled with solid white. Both may appear white against a white worksheet, and checking only a color number may not express the distinction you intend. To require that both cells actually have a fill pattern, test Interior.Pattern as well as Interior.Color. For a plain solid-fill check, xlNone identifies no fill; patterned fills need more careful handling because their pattern and foreground color can affect what is visible.

Comparing two cells or testing a specific color

To compare two cells directly, read their properties in the same way: compare Range("A1").Interior.Color with Range("B1").Interior.Color. The result is True when the values are equal and False otherwise. In a loop, replace the reference to B1 with whichever cell should act as the color standard.

To test for a known RGB color, use Excel VBA’s RGB function. For example, a red fill created with red 255, green 0, and blue 0 can be compared with RGB(255, 0, 0). This is useful when the reference color is fixed rather than stored in a sample cell. Be precise about the color you expect: a visually similar shade is not necessarily the same numeric color.

If you only need to color cells that meet a rule, do not necessarily compare fills at all. A macro can test the cell’s value, then format it. That keeps the decision based on the data and avoids relying on a style that someone might later change. For instance, test whether a date is earlier than today to identify overdue items, rather than assuming all overdue dates remain red forever.

Common problems and practical checks

  • Nothing matches, although the cells look alike: Check whether the visible fill comes from conditional formatting. If so, use DisplayFormat.Interior.Color in a macro, or compare the rule’s underlying condition instead.
  • White cells behave unexpectedly: Confirm whether they have an explicit white fill or no fill. Test the fill pattern if that distinction matters.
  • A font or border color is being compared: Use the appropriate property. Fill uses Interior.Color, text uses Font.Color, and borders have their own color properties.
  • Only some cells should be checked: Set checkRange to the intended range, and make sure the reference cell is on the worksheet assigned to ws. Qualifying ranges with a worksheet avoids accidentally reading from another active sheet.
  • The macro is blocked or disappears after saving: Check the file type and macro security settings. Use .xlsm to preserve VBA, and do not enable code from an untrusted source.

When adapting the example, avoid comparing a cell to itself by mistake, and make sure the reference cell is not being changed by another macro. The sample code is intentionally read-only: it counts matches but does not highlight, clear, or overwrite any cell formatting.

When to use VBA—and when not to

VBA is useful when you need to inspect many cells, repeat a comparison, or automate an action based on a fill. It is less convenient when the workbook must work in Excel for the web, when macros are disabled, or when other people need to understand the decision from the worksheet alone.

For a visual rule such as highlighting duplicate values, Excel’s built-in conditional formatting is usually a better fit than a macro that checks existing colors. In Microsoft 365 for Windows, relevant options are under Home > Conditional Formatting. A formula-based rule can express logic directly, while a VBA color check asks what formatting is already applied. These approaches solve different problems: conditional formatting decides how cells should look; VBA can inspect existing formatting and take an action.

There is no built-in worksheet function that directly returns a cell’s fill color for an ordinary formula. If a workflow depends on color as data—for example, green means approved—consider adding an explicit status value such as “Approved” and basing formulas, filters, and conditional formatting on that value. That is usually easier to maintain than treating a visual cue as the source of truth.

Frequently Asked Questions

Can Excel formulas check whether two cell colors match?
No standard worksheet formula directly compares fill colors. VBA can compare Interior.Color, while a formula can compare the underlying values that caused a conditional format.

Does Interior.Color detect conditional formatting?
It reads the cell’s directly applied fill, not necessarily the fill currently displayed because of a conditional-formatting rule. In a VBA macro, use DisplayFormat.Interior.Color when you need the displayed color.

Can I compare a cell’s font color instead of its fill?
Yes. Compare the cells’ Font.Color values for font color. Interior.Color is for the cell’s fill.

Why do two white-looking cells return different results?
One may have no fill while the other has a solid white fill, or the cells may use slightly different colors that are hard to distinguish on screen. Check both the fill pattern and color if no fill must count as different.

Will this macro work in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop version of Excel; Excel for Windows and Excel for Mac support VBA, with some interface differences.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.