How to Count Cells by Background Color in Excel with VBA

As of Excel 2019, there are no built-in formulas to count cells based on background color (also called fill color or interior color). However, this task can be accomplished using a pair of user-defined functions (UDFs) in VBA.

You might be wondering why anyone would need to count cells by their background color — especially when most data sets include better values or criteria for analysis. But if you’ve landed here, you probably have a specific use case.

In one of my dashboards, I used background color to flag errors in a complex workbook. Conditional formatting highlighted cells red whenever error conditions were met. Rather than creating helper columns to track these errors, I used the CountBackgroundColor function described in this article to summarize the “red” cells, enabling a streamlined, visual dashboard for monitoring and fixing issues.

This article demonstrates two VBA functions:

• GetBackgroundColor
• CountBackgroundColor

Instructions

Create a New VBA Module

1. From the Developer ribbon in Excel, click Visual Basic.
2. In the Visual Basic for Applications window, go to Insert > Module.
3. Paste the following code into the new module and close the editor.

Option Explicit

Function GetBackgroundColor(rngCell As Range) As Long
  If IsObject(rngCell) Then
    If rngCell.Cells.Count = 1 Then
      GetBackgroundColor = rngCell.Interior.ColorIndex
      Exit Function
    End If
  End If

  GetBackgroundColor = CVErr(xlErrRef)
End Function

Function CountBackgroundColor( _
  ByVal backgroundColor As Long, _
  ParamArray dataRanges() _
) As Long

  Dim paramIndex As Long
  Dim cell As Variant
  Dim matchCount As Long: matchCount = 0
  Dim checkedRange As Range

  For paramIndex = LBound(dataRanges) To UBound(dataRanges)
    If IsObject(dataRanges(paramIndex)) Then
      Set checkedRange = Intersect( _
        dataRanges(paramIndex), _
        dataRanges(paramIndex).Parent.UsedRange _
      )

      For Each cell In checkedRange
        If cell.Interior.ColorIndex = backgroundColor Then
          matchCount = matchCount + 1
        End If
      Next cell
    End If
  Next paramIndex

  CountBackgroundColor = matchCount
End Function

Usage

GetBackgroundColor

The GetBackgroundColor(reference) function returns the color index of the specified cell’s background color.

Parameters:

• range1 (required): A single cell or a range to evaluate.

CountBackgroundColor

The CountBackgroundColor(color_index, range1, [range2], …) function counts the number of cells matching the specified color index within one or more ranges.

Parameters:

• color_index (required): Numeric value of the desired color.
• range1 (required): A single cell or a range to evaluate.
• [range2, …] (optional): Additional ranges to include.

Results

After filling cells in E2:G16 with red, yellow, or green, here’s how the functions work:

Example 1: Red Cells

• The formula =GetBackgroundColor(E3) returns the value 3 which corresponds to the color index for red.
• The formula =CountBackgroundColor(3, E2:G16) returns the value 13 for the 13 red cells.

Example 2: Yellow Cells

• The formula =GetBackgroundColor(E6) returns the value 6 which corresponds to the color index for yellow.
• The formula =CountBackgroundColor(6, E2:G16) returns the value 5 for the 5 yellow cells.

Example 3: Green Cells

• The formula =GetBackgroundColor(G6) returns the value 43 which corresponds to the color index for green.
• The formula =CountBackgroundColor(43, E2:G16) returns the value 6 for the 6 green cells.

Known Limitations

Avoid passing whole-column references (e.g., A:A) as arguments in large workbooks. The function checks each cell individually, and large used ranges (especially whole-column references with data) can significantly slow performance or cause Excel to freeze. If the used range is mostly empty, performance should remain acceptable.

Summary

Although Excel doesn’t natively support counting by background color, these simple VBA functions provide an effective workaround. Whether you’re building dashboards or tracking conditional formatting, GetBackgroundColor and CountBackgroundColor are practical tools to streamline visual data analysis. Use them with care in large data sets for the best results.