Erlandsen Data ConsultingCount 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)
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.
How can I get automatic calculation, when I put at color more in the columns ?
I use Excel 2007
Changing a cell’s background color will not trigger any calculations in Excel.
You can make the CountByColor function update more frequently by adding the line below to the function code, right after the first Dim-statement line:
Now the function will re-calculate every time a calculation is triggered, e.g. when entering new content or pressing F9.