Excel 2002 count color

H

halcyon

I would like to count the cells that have a value in font color red and not
count the cells in font color black, how can i achieve this?
 
H

halcyon

Hi Mike H, thank you so much, I am very simple at this and have tried to copy
and paste but I have no result, if I want to copy the formula or fuction and
apply to excel where do I paste these long winded functions?
 
M

Mike H

Hi,

OK, here's a very simple one. Alt+F11 to open VB editor. Right click
'ThisWorkbook' and insert module and paste the code below in.

call with

=countcolour(a1:a12,3)

Set the range to suit your. 3 equals red so change that to count a different
colour


Function countcolour(rng As Range, cindex As Integer) As Long
For Each c In rng
If c.Font.ColorIndex = cindex Then
countcolour = countcolour + 1
End If
Next
End Function

Mike
 
M

Mike H

I should heve mentioned that this doesn't work if the font colour is derived
from a condiitional format.

You can count by conditional format colour but it's not easy. There's a
method on the XLDYNAMIC website but currently it appears to be down
 
Top