Count by color font help

**Greed** · 07-15-2011, 02:27 PM

Need help to count by font color -
I have added a module from this site to 'count by font color' which works well at home with my Excel 2010 version, but when I try to use it at work (Excel 2003) it malfunctions in 2 ways.
First, the formula 'GetFontColorIndex' for automatic (black) returns -4105, which I am not sure about as I thought it should be 1, but the non-automatic (black) returns the number 1so I am at a loss....
Second, with the command 'CountFontColor' I end up counting all cells in the range, including the cells that are blank, which I do not want.... Any help would be much appreciated!

**venkat1926** · 07-16-2011, 12:44 AM

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

**royUK** · 07-16-2011, 04:01 AM

See this post

http://www.excelforum.com/excel-gene...ill-color.html

Also, most important, remember these functions will not work with colours generated by Comditional Formatting

**alansidman** · 07-16-2011, 07:57 AM

Here is a valuable tutorial on the subject

http://www.datapigtechnologies.com/f...rtonColor.html

Alan

**carlito_1985** · 07-17-2011, 12:07 AM

Hi,

I use 2003 and use this function quite frequently.

The UDF I use is:

Please Login or Register  to view this content.

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

**Greed** · 07-18-2011, 09:14 AM

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,"")