How to count cells colored red?

[JoeU2004]

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

 

[FSt1]

hi
i know of no built in functions that will do colors so you UDF is about the
only way.
you might want to look at chip's site. he has a number of color functions to
count, sum, sort, other.
http://cpearson.com/excel/colors.htm

Regards
FSt1

 

[JoeU2004]

Thanks.


----- original message -----

hi
i know of no built in functions that will do colors so you UDF is about
the
only way.
you might want to look at chip's site. he has a number of color functions
to
count, sum, sort, other.
http://cpearson.com/excel/colors.htm

Regards
FSt1

 

[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.