Count by color

With the built-in COUNTA()-function it's not possible to count cells in a range depending on each cells background color.



With the custom function below you can count the cells depending on their background color:



Function CountByColor(InputRange As Range, ColorRange as Range) As Long

Dim cl As Range, TempCount As Long, ColorIndex As Integer

ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex

TempCount = 0

For Each cl In InputRange.Cells

If cl.Interior.ColorIndex = ColorIndex Then

TempCount = TempCount + 1

End If

Next cl

Set cl = Nothing

CountByColor = TempCount

End Function

This function is used in the same way as built-in worksheetfunctions. InputRange is the range that the function is going to


count, ColorRange is a reference to a cell containing the background color you wish to count.

E.g. used like this in a worksheet formula: =CountByColor(A1:A100,C1)