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:
In Excel 5/95 the variables InputRange and ColorRange must be declared as Variant instead of Range.
This function can easily be adapted for use with other worksheetfunctions that you wish to use on cells with different background colors.