How to count cells colored red?



I believe this question has been asked and answered before, but sigh, I did
not pay attention at the time, and it is hard to find the threads now.

Using an Excel formula and no VBA, how can I count the cells that are
colored red due to font formatting (e.g, using Font.ColorIndex in VBA)?

The CELL("color") function does not seem to give distinctive results in that
case. (Only if negative values are colored, which is not my situation.)

I do have an alternative: I can write a UDF to count them.

But I wonder if there is any way to do this with Excel formulas alone.

FYI, my UDF is below. Any suggestions for improvements would be welcomed.
For example, I do not like hardcoding the color index 3 (red).

Function countRed(rng As Range) As Double
Dim cell As Range
For Each cell In rng
If cell.Font.ColorIndex = 3 Then countRed = countRed + 1
Next cell
End Function

T. Valko

It's not impossible but it's also not exactly what you had in mind.

Using a helper column (can only test one cell at a time!) you can use the
GET.CELL macro function and then call this through a worksheet formula.
However, it suffers from the same problem as most other VBA methods for
doing this in that a font color change does not trigger a calculation so the
formulas won't update when the font color is changed.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question