Counting Cells by font color problem

**markwilest** · 09-27-2007, 04:28 PM

I found this code online that counts the number of cells in a certain range with a specified font color. (Each of my cells have either red or black text, and I wanted to sum the number of cells with black text, and the number of cells with red text.) The code can be found here:

http://www.xldynamic.com/source/xld.ColourCounter.html

I thought I was using the code incorrectly since I'm fairly new to excel and the results returned were way off. However, it appears that for some reason it is counting cells with no text. I checked this by changing the default color of the blank cells within the range to some other color (not red or black). I then got accurate results.

Does anybody have any idea on why this is happening, or how I can prevent it. Having to assign a font color to the blank cells greatly decreases the usefulness of this code.

Thanks

Mark

**VBA Noob** · 09-27-2007, 04:38 PM

What formula you using

As the site mentions defauly is cell colour.

Determines cell colour (interior) or text colour (font)
' Default is cell colour

Need to use a formula like (Note the true for font instead of cell)

=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3))

Also you have to press F9 to recalc which also may look like it's not working

VBA Noob