MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Counting Colours


Posted by Ian on September 06, 2001 8:18 AM

Hi All

I'm looking for way to count the number of time a font colour occurs within a set range

Ian


Posted by Juan Pablo on September 06, 2001 8:58 AM

How about this function ? it doesn't recalculate automatically, i don't know why... Use it in Excel !!!

Public Function FontColor(Rng As Excel.Range, Range_font As Excel.Range) As Long
'Function that counts number of occurrences of a font color according to the Range_font font color
Dim i As Long
i = 0
For Each cll In Rng
If cll.Font.Color = Range_font.Font.Color And Not IsEmpty(cll) Then i = i + 1
Next cll
FontColor = i
End Function

Juan Pablo

Posted by Aladin Akyurek on September 06, 2001 10:57 AM

> I'm looking for way to count the number of time a font colour occurs within a set range

You can also use the condition that you use for font coloring (via conditional formatting or otherwise) to do the counting by means of a formula.

Aladin

Posted by Barrie Davidson on September 06, 2001 12:49 PM

To make your function recalculate automatically....

Put,
Application.Volatile
as the first line of your user-defined function.

Regards,
BarrieBarrie Davidson