Thread: Counting cells based on their colour

Hi.

In the attached workbook I am manually adding 1 for Green, 2 for Yellow and 3 for Red in column C, then COUNTIF to get totals. I would rather Excel recognise and count all cells with same colour i.e. all Green, all Yellow and all Red. Can excel count by colour? If so How is it done? Thanks.

Hi fithawk,
How about a User Defined Function (UDF) to do your counting. See the code in the example and the way it works.

Function CountMyColors()
    Dim LastBRow As Double
    Dim RowCtr As Double
    Dim ColorCtr As Double

    LastBRow = Cells(Rows.Count, "B").End(xlUp).Row
    ColorCtr = 0
    
    For RowCtr = 5 To LastBRow
    If Cells(RowCtr, "B").Interior.Color = ActiveCell.Interior.Color Then
        ColorCtr = ColorCtr + 1
    End If
    Next RowCtr
    
    CountMyColors = ColorCtr

End Function

This will count the number of cells in B5 to B end that are the color of the active cell that the formula is in.

Hi,
You need to click in each of the colored cellls and put a space after the UDF and press enter. This will recalculate the count for each color.

The real answer is counting colors is very hard in Excel. I like your hidden column C better than trying to work with colors.

teylyn said it correctly when she said "colors aren't data"