Count cells by "Background" color & by "FONT" Color

[martinargimon]

HI There,
I haven't been able to get the second Function listed in the heading of this Thread ( "Count cells by "FONT" color" ) to work.
I have tried 2 different Code approaches and still does not work.
The first function "Count cell by Background' works perfectly well.

I have attached the sheet "MEM Database ......", and if you have a look at the 'Site Plan" worksheet, right at the bottom right hand corner ( BU113:CP142), you will find the 2 practical examples of both Functions at work.
Although the code clearly 'picks up' the font color from the 'input' cell , it actually counts ALL the cells in the range and NOT just the ones with the same FONT color.
I would appreciate your comments?
Thanks very much
Regards
MArtin

 

[Marc L]

Hi,​

​

your bad as all cells in CA129:CB142 have the same red font color even the empty cells !​

As you can easily check yourself (before creating such thread) …​

​

 

[martinargimon]

Hi Marc L
I wasn’t aware that the function would also pick up ‘empty cells’ even if the default font was ‘red’.
Sorry about this. VBA is at time quite tricky even in the small details.
thanks a lot
Regards
Martin

 

[6StringJazzer]

Perhaps this answer will actually help to solve your problem. I am assuming you only want to count non-blank cells. Also, you are returning a count, which should be a Long, not a Double.

Function CountColour(pRange1 As Range, pRange2 As Range) As Long

Application.Volatile
Dim rng As Range
For Each rng In pRange1
    If rng <> "" And rng.Font.Color = pRange2.Font.Color Then
        CountColour = CountColour + 1
    End If
Next

End Function

 

[6StringJazzer]

I wasn’t aware that the function would also pick up ‘empty cells’ even if the default font was ‘red’.

All formatting is present even if the cell is empty.

 

[martinargimon]

Thanks a lot 6stringjazzer!
Kind regards
Martin