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
- Save a copy of the workbook first. Macros cannot be undone with Ctrl+Z.
- In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module.
- Paste the code into the module, then return to Excel.
- Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) if you need to retain the macro. - 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
- Can Git Track Excel Files? Version Control Explained
- Can You Use Excel to Randomize a List? Yes, Here’s How
- Calendar Template in Microsoft Excel: Choose and Use
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA MsgBox Arguments: Buttons, Icons, and ExamplesIn VBA, MsgBox displays a dialog and can return the button the user clicked. Its arguments are Prompt,…
- Copy and Paste Dynamically Using VBA in ExcelTo copy a range that changes size as your data grows, use VBA to find its last row…
- Excel VBA: How to Exit a For Next Loop Early in Your MacroYes. In Excel VBA, put Exit For inside a For...Next loop to leave it as soon as a…
- Excel VBA AutoFilter with an Array: A Practical GuideYes. In Excel VBA, pass a one-dimensional array of the values you want to keep to the range’s…
- Excel SEQUENCE Macro: Generate Number Series in VBAIf you want a sequence of numbers in Excel, the simplest option is usually the SEQUENCE worksheet function,…
- Excel VBA AutoFilter Array: Filter Multiple ValuesTo filter a column for several exact values with Excel VBA, pass an array to Criteria1 and set…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.