Number of text cells by color

[PomDave]

Hi All,

Is there a formula/macro that can sum the number of cells in a column, or a range of columns, that are red and those that are black?

Rgds,
Dave R.

 

[shamimarita]

Hello PomDave,

Yes, this is possible with a small VBA user-defined function. Excel formulas cannot directly count cells by font color, so a macro/UDF is the best option.

Please try this VBA function:

Function CountTextByFontColor(rng As Range, colorCell As Range) As Long
    Dim cell As Range
    Dim countResult As Long
    
    For Each cell In rng
        If cell.Value <> "" And Not IsNumeric(cell.Value) Then
            If cell.Font.Color = colorCell.Font.Color Then
                countResult = countResult + 1
            End If
        End If
    Next cell
    
    CountTextByFontColor = countResult
End Function

Then use it like this in Excel:
=CountTextByFontColor(A1:A100,C1)

Here, A1:A100 is the range you want to check, and C1 should contain sample text formatted with the color you want to count, for example, red or black.

For example:
=CountTextByFontColor(A1:A100,C1)

counts text cells with the same font color as C1.
=CountTextByFontColor(A1:A100,D1)

counts text cells with the same font color as D1.

If you mean the cell fill/background color instead of the text/font color, replace this line:

If cell.Font.Color = colorCell.Font.Color Then

with:

If cell.Interior.Color = colorCell.Interior.Color Then

After adding or changing colors, press Ctrl + Alt + F9 to recalculate the workbook.

 

[PomDave]

Sorry I haven't replied earlier, personal issues.

Thanks for that I'll give it a try.

Rgds,
Dave R.

 

[shamimarita]

Hello PomDave,

Let us know your feedback and solution.

 

[davidbaggins]

Hi All,

Is there a formula/macro that can sum the number of cells in a column, or a range of columns, that are Geometry Dash red and those that are black?

Rgds,
Dave R.

You can use a simple VBA user-defined function:

Function CountFontColor(rng As Range, sample As Range) As Long
    Dim c As Range

    For Each c In rng
        If c.Font.Color = sample.Font.Color Then
            CountFontColor = CountFontColor + 1
        End If
    Next c
End Function

 

[alexiaamber]

Hi All,

Is there a formula/macro that can sum the number of cells in a column, or a range of columns, that are red and those that are black?

Rgds,
Dave R. 1000 games

Hi Dave,

Yes, this can be done with VBA, but the important point is that Excel’s normal COUNTIF/COUNTIFS functions don’t directly count cells based on their fill color.

If the colors are applied manually, a small VBA user-defined function is probably the simplest approach. Another option is to use a helper column if the color represents a particular status or category; that is generally more robust than relying on formatting.

If the colors are coming from conditional formatting, that’s a different case, because the displayed color is based on the underlying rule rather than the cell’s actual fill color. In that situation, it’s better to count the condition that produces the color rather than the color itself.