MS Excel: Count the number of cells with a particular font color in Excel 2003/XP/2000/97

Question: In Excel 2003/XP/2000/97, how can I count the number of cells that have a particular font color. For example, I want to count the number of cells that have a red font color. How can I do this?

Answer: Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

In this example, we have values in column A that have a red font. So first we need to determine the index number for the red font. To do this, we've created a function called GetFontColor. In cell D2, we've created the following formula:

=getfontcolorindex(A3)

This function returns 3 which is the color index for the red font used in column A, specifically cell A3.

Next, we want to count how many cells have a red font in column A. To do this, we've created the following formula in cell D4:

=CountFontColor(A2:A12,3)

This formula returns the number of cells in range (A2:A12) that have a font color index of 3.

You can view the VBA code by pressing Alt-F11.

Macro Code:

The VBA code for the GetFontColorIndex function is:

Function GetFontColorIndex(pRange As Variant) As Integer

Set pRange = pRange.Areas(1)

GetFontColorIndex = pRange.Cells(1, 1).Font.ColorIndex

End Function

The VBA code for the CountFontColor function is:

Function CountFontColor(pRange As Variant, pIndex As Integer) As Integer

    Dim LTestRange As Variant
    Dim i As Long, j As Long, m As Long, n As Long
    Dim LTotal As Integer

    Set pRange = pRange.Areas(1)

    LTotal = 0

    m = pRange.Rows.Count
    n = pRange.Columns.Count
    LTestRange = pRange.Value

    For i = 1 To m
        For j = 1 To n
            If pRange.Cells(i, j).Font.ColorIndex = pIndex Then
                LTotal = LTotal + 1
            End If
        Next j
    Next i

    CountFontColor = LTotal

End Function