Recent Addins ADO Application Arrays Charts Commandbars Count Dates Dialogs Events File access Files & Folders Functions Games Help Import & Export Lookup Off topic Other Outlook PivotTables Power Query Printing Problem solving Read me Searching Sorting Sound Strings Sum Time VBA programming VBE Workbooks Worksheets

Count by cell background color

 1999-08-14    Count    2    215

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.