Count Cells by Color in Excel VBA: A Practical Guide

VBA & macros · Updated November 19, 2026

To count cells by fill color in Excel VBA, run a macro that compares each cell’s fill with a sample cell’s fill. The macro below lets you select both ranges and displays the count; if the colors come from conditional formatting, use the separate version that checks the displayed color.

What Excel means by a cell’s color

Excel can show color in several ways: a cell’s background fill, its font color, a border, or an icon or data bar added by conditional formatting. These are different properties. The examples here count background fills, which is what most people mean when they ask to count cells by color. A font-color count needs a similar comparison using Font.Color instead of Interior.Color.

Color is a visual format, not the cell’s underlying value. A blue cell might contain “Paid,” a date, or nothing at all. VBA can count the format without interpreting the value, but a color count may be less reliable than counting a status field when the color represents business information. Consider keeping an explicit status such as “Paid” in a separate column and using color as a visual aid.

The macro compares the sample cell’s fill color with each cell in the range. It counts cells with a fill whose color matches; it does not count cells with no fill as though they were white. The comparison is about the stored fill color, not whether two colors merely look similar on a particular monitor.

A macro for cells with manually applied fills

This version is for colors applied directly through the Fill Color control, rather than colors displayed by a conditional-formatting rule. It asks you to select the cells to inspect and then a filled sample cell. The sample can be outside the range being counted.

Sub CountCellsByColor()
    Dim targetRange As Range
    Dim sampleCell As Range
    Dim cell As Range
    Dim targetColor As Long
    Dim total As Long

    ' Let the user select the cells to inspect.
    On Error Resume Next
    Set targetRange = Application.InputBox( _
        Prompt:="Select the cells to count.", _
        Title:="Count cells by fill color", Type:=8)
    If targetRange Is Nothing Then Exit Sub

    ' Ask for a cell that has the fill color to count.
    Set sampleCell = Application.InputBox( _
        Prompt:="Select one filled sample cell.", _
        Title:="Choose sample color", Type:=8)
    If sampleCell Is Nothing Then Exit Sub
    On Error GoTo 0

    Set sampleCell = sampleCell.Cells(1, 1)
    If sampleCell.Interior.Pattern = xlNone Then
        MsgBox "Choose a sample cell with a fill color.", vbExclamation
        Exit Sub
    End If

    targetColor = sampleCell.Interior.Color
    For Each cell In targetRange.Cells
        If cell.Interior.Pattern <> xlNone Then
            If cell.Interior.Color = targetColor Then total = total + 1
        End If
    Next cell

    MsgBox total & " cell(s) match the sample fill color.", vbInformation
End Sub

The code uses Interior.Color, which returns a numeric color value, and checks Interior.Pattern so that an unfilled cell is not treated as a colored cell. It checks every cell in the selected range, including blank cells that have a fill. If you want to count only filled-in values as well as color, add a test such as Len(cell.Value2) > 0 to the condition; decide whether formulas returning an empty string should count before using that test.

How to add and run the VBA macro

  1. Save a copy of the workbook first. Macros cannot be undone with Ctrl+Z.
  2. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module.
  3. Paste the code into the module, then return to Excel.
  4. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the macro.
  5. Press Alt+F8, select CountCellsByColor, and choose Run. Select the target range and sample cell when prompted.

On Mac, Excel also supports VBA macros, though keyboard shortcuts can depend on the keyboard and macOS settings; use the Developer tab’s Visual Basic and Macros commands if the Windows shortcuts do not work. Excel for the web does not run VBA macros. Open the workbook in the desktop Excel app to use this code. Only enable macros in workbooks you trust.

Use a worksheet function when a live cell result is useful

A VBA user-defined function can return a count in a worksheet cell instead of showing a message box. With colored cells in A2:A6, suppose three have a blue fill and two have a red fill; put a blue-filled sample in D1. After adding this function to a standard VBA module, enter =CountCellsByColor(A2:A6,D1) in another cell. It returns 3, because three cells in the range match D1’s fill.

Function CountCellsByColor(dataRange As Range, colorSample As Range) As Long
    Dim cell As Range
    Dim targetColor As Long

    ' Use the first cell if the sample reference has multiple cells.
    Set colorSample = colorSample.Cells(1, 1)
    If colorSample.Interior.Pattern = xlNone Then Exit Function

    targetColor = colorSample.Interior.Color
    For Each cell In dataRange.Cells
        If cell.Interior.Pattern <> xlNone Then
            If cell.Interior.Color = targetColor Then
                CountCellsByColor = CountCellsByColor + 1
            End If
        End If
    Next cell
End Function

A worksheet function is convenient, but formatting changes do not behave like ordinary value changes: changing a fill usually does not trigger formula recalculation. If the displayed count is stale, force a calculation with Ctrl+Alt+F9 on Windows, or edit and re-enter the function. This function checks directly applied fills only; it is not a reliable way to read conditional-formatting display colors. For a count that must update predictably, a macro you run after formatting changes or a data-based status field may be a better choice.

Counting colors produced by conditional formatting

When a rule applies the color, Interior.Color may describe the cell’s underlying fill rather than the color currently shown. Excel exposes the rendered formatting through DisplayFormat. Use this macro for the visible fill color created by conditional formatting:

Sub CountDisplayedCellsByColor()
    Dim targetRange As Range
    Dim sampleCell As Range
    Dim cell As Range
    Dim targetColor As Long
    Dim total As Long

    ' Select the range, then a sample showing the color to count.
    On Error Resume Next
    Set targetRange = Application.InputBox( _
        Prompt:="Select the cells to inspect.", _
        Title:="Count displayed colors", Type:=8)
    If targetRange Is Nothing Then Exit Sub

    Set sampleCell = Application.InputBox( _
        Prompt:="Select one cell currently showing the target color.", _
        Title:="Choose displayed color", Type:=8)
    If sampleCell Is Nothing Then Exit Sub
    On Error GoTo 0

    Set sampleCell = sampleCell.Cells(1, 1)
    If sampleCell.DisplayFormat.Interior.Pattern = xlNone Then
        MsgBox "Choose a sample cell displaying a fill color.", vbExclamation
        Exit Sub
    End If

    targetColor = sampleCell.DisplayFormat.Interior.Color
    For Each cell In targetRange.Cells
        If cell.DisplayFormat.Interior.Pattern <> xlNone Then
            If cell.DisplayFormat.Interior.Color = targetColor Then
                total = total + 1
            End If
        End If
    Next cell

    MsgBox total & " cell(s) show the matching fill color.", vbInformation
End Sub

Run this after the worksheet has recalculated so the conditional-formatting rules reflect current values. DisplayFormat is useful in a regular macro, but it should not be used as the basis for a worksheet UDF: Excel does not reliably support it in that context. If rules use font colors rather than fills, compare DisplayFormat.Font.Color instead. A rule may also use a gradient or data bar, which is not simply a single solid fill color to count.

Color matching, themes, and practical limits

The examples compare the numeric color returned by Excel, rather than the color’s name or a text label. Two fills that look nearly identical can have different values, while colors from a workbook theme can be affected by theme choices. If the count seems wrong, select the sample from the same formatting source and inspect whether the cells truly use the same fill, rather than relying on visual similarity.

  • Patterned fills: The code compares the fill’s color property and excludes cells with no fill. For complex patterns, matching the color alone may not mean the entire pattern is identical.
  • Merged cells: A range containing merged areas can be awkward to interpret because a merged region spans multiple cells. Count the top-left cell of each merged area if the intended unit is one merged region, rather than assuming every cell position represents a separate visible block.
  • Filtered lists: The loop counts all cells in the selected range, including rows hidden by a filter. If you only want visible rows, the macro needs additional logic to inspect visible cells.
  • Large ranges: Checking cells one at a time is fine for many ordinary ranges, but a very large range can take longer. Narrow the selection to the relevant column and rows.

When to use a color count and when to choose another method

Use the macro when color itself is the information you need to audit—for example, to check how many manually highlighted cells appear in a review range. Use a status column and an ordinary count when color represents a category such as “Approved.” A formula that counts text is generally easier to maintain, share, and recalculate because it depends on cell values rather than visual formatting.

Other options include filtering by cell color using Excel’s filter controls and reading the visible result, or using conditional formatting to highlight values that meet a rule. Those approaches can be quicker for a one-time inspection, but they do not necessarily produce a reusable numeric count. A VBA macro is useful when the workflow is repeated and desktop Excel is available; it is less suitable for a file that must work in Excel for the web or in environments where macros are blocked.

Common problems and fixes

  • The macro is missing from Alt+F8: Confirm the code is in a standard module, not a worksheet or ThisWorkbook module, and that the file is saved as .xlsm.
  • The count is zero: Check that the selected sample is filled, the target range is correct, and the cells use the same fill property. For rule-based colors, run the DisplayFormat version.
  • The count includes blank cells: This is expected when blank cells have a fill. Add a value check if blank cells should be excluded.
  • The count does not change after recoloring: Run the macro again. A worksheet UDF may also need forced recalculation because a formatting-only edit may not trigger it.
  • Colors look the same but do not match: Reapply one exact fill to the cells or use a sample cell from the same source. Similar-looking theme colors are not necessarily identical values.

Frequently Asked Questions

Can Excel count cells by color without VBA?
Excel does not provide a standard worksheet function that counts arbitrary fill colors directly. Filtering by color can help inspect a list, while a status column lets you count categories with ordinary worksheet formulas. VBA is one option when the fill itself must be counted.

Does this macro count font color?
No. The examples compare background fills through Interior.Color. For font color, compare Font.Color instead; for conditional formatting, use the corresponding DisplayFormat.Font.Color property in a macro.

Will the count update automatically when I change a fill?
The macro updates when you run it again. A worksheet UDF may not recalculate just because formatting changed. Force a calculation or use a macro when you need a fresh count after changing colors.

Can I use the code in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA; for a browser-based workflow, consider tracking the category in cell values instead.

Does the macro count hidden or filtered-out rows?
Yes. The supplied loops inspect every cell in the selected range, including cells in hidden rows. If the requirement is to count only visible rows, the macro must be adjusted to work with visible cells.

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.