Counting cells by font color

[Cassie]

I just upgraded to excel 2007 but I still can't figure out what I need to do!
I need to come up with a way to count non-blank cells by font color. I have
a list of 10 names and I currently have a counta function set to count only
the non-blank cells. Now my boss wants to see that number turned into a
calculation that counts the number of active names (in this case black font),
and ignore the inactive names (gray font). How would I write that to produce
anything other than the error message I've become so used to?

 

[Jacob Skaria]

There is no built-in excel function to do this. But you can use a UDF that
looks at the range and returns the sum of font color. But that function
will not
recalculate if you change color. Every time you change the color you will
need to recalculate or wait excel to recalculate...

To install the UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

=ColorSum(A1:A10)

Function ColorSum(varRange As Range)
Dim cell As Range
For Each cell In varRange
If cell.Text <> "" And cell.Font.ColorIndex = 1 Then ColorSum = ColorSum + 1
Next
End Function

If this post helps click Yes

 

[Squeaky]

Hi Cassie,

What turns the names black or gray? If it is done by formula you can use the
result you get to count the instances. If you change them by hand perhaps you
can use the column next to the name and place a marker in it, or even
drag-copy the name into that column when you go through the names to change
their color, then count the names. Seems like a lot of hassle to count 10
names.