» Count by color using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions 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.
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.