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" wrote:

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