Count by color font help

I am not sure whether I am correct.

excel 2007 and above have provision for counting color fonts but excel 2003 does not have;. In excel 2003 your have to write a user defined function. see this url

http://www.techonthenet.com/excel/qu...font_color.php

Hi,

I use 2003 and use this function quite frequently.

The UDF I use is:

Function countfontcolour(Colour_Sample As Range, Cell_Range As Range)
Dim rCell As Range
Dim lCol As Long
Dim lResult As Long
Application.Volatile
lCol = Colour_Sample.Font.ColorIndex

For Each rCell In Cell_Range
If rCell.Font.ColorIndex = lCol Then
lResult = 1 + lResult
End If

Next rCell

countfontcolour = lResult

End Function

To use it enter:

=countfontcolour(Colour_Sample,Cell_Range)

so if A1 contains the colour you want to count (eg. Value with Red Font) and D1:D50 is the range you want to count those with red font then use it like so:

=countfontcolour(A1,D1:D50)

Thanks

Thanks everyone - I already had the module insterted and counting, the bigger problem was that it was counting BLANK cells also, which I did not want, but had to include in the range since I was creating the spreadsheet to be used by others. I finally changed the formula to subtract the blanks - kind of messy but I couldn't see any other way. I think it might have been easier if I was counting a colour, rather than automatic black.....
The formula looks like this:
=CountFontColor(A4:A15,-4105)-COUNTIF(A4:A15,"")