How to Count Cells by Fill Color in Excel (UDF Method)

You probably may have received a 5,000 row database containing cells with multiple fill colors, and you really just want to find out how many cells are colored with a specific fill color.

• 

There are two ways to get this done.

Approach 1: Create a table (select the entire range of data and Ctrl+T) or set up data filters (select the entire range of data and Ctrl+Shift+L). Click the now created drop-down in the column header and select Filter by Color

• 

Automatically, this hides rows with other colors that do not match the criteria you selected in the Filter by Color, and in the status bar, you can see the number of rows that meet the color filter criteria

• 

But what if you need this count status output in a cell? This brings us to Approach 2.

Approach 2: Use a User Defined Function. Excel does not have an in-built feature to count cells by color, but you can create a code to do this and print out the output to a cell.

User defined functions (UDF) are a type of VBA code that unlike subs, return their output into a cell. Once you program a UDF, it works just the same way a regular Excel function (e.g. =SUM(), =IF(), =VLOOKUP()) would work. Unlike these Excel functions however, it is not updated automatically and has to be executed afresh when the variables change.

UDFs, just like other VBA codes are programmed by navigating to the Visual Basic Editor (Developer>>Visual Basic or Alt+F11), inserting a new module, and placing the code within the created module:

The UDF to count cell colors is below:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

' the above creates a function called "ColorFunction" with three variables in this order: rColor, rRange, SUM


Dim rCell As Range
Dim lCol As Long
Dim vResult

lCol = rColor.Interior.ColorIndex

If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else

For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell

End If

ColorFunction = vResult

End Function

Place the above code in the module, save your document as a macro-enabled format (.xlsm or .xlsb), return to the worksheet and use the function as shown below:

• 

As seen above, the function takes two key variables - a cell with the fill color of interest, and the complete range of cells you wish to count. The result is below:

• 

Note: Unlike regular Excel functions, this UDF is not automatically updated when the cell colors change. You have to step into the formula and hit 'Enter' to refresh its value when cell colors are updated.