Count by cell background color

 1999-08-14    Count    2    58

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.


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

OPE | 2009-09-22 11:42:33 (GMT)

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:

Application.Volatile

Now the function will re-calculate every time a calculation is triggered, e.g. when entering new content or pressing F9.

Søren Noack | 2009-09-22 09:57:29 (GMT)

How can I get automatic calculation, when I put at color more in the columns ?

I use Excel 2007