VBA Count Font Colour

[harky]

Hi, i found this code but it kindly lag somehow..
it keep counting and got the % show on the excel bar.
And it also included empty cell that has font colour

=CountColour(F2:BO2,$A$1)

Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double 'Update by Extendoffice Application.Volatile Dim rng As Range For Each rng In pRange1 If rng.Font.Color = pRange2.Font.Color Then CountColour = CountColour + 1 End If Next End Function

 

[6StringJazzer]

A change in font color does not indicate a change to a cell, so a function like this must be Volatile. That means it runs any time there is any change in the worksheet, even if the change does not affect font color. So it recalculates a lot. How many cells have this formula?

Font color is a property of a cell. It doesn't matter if the cell is empty, it still has a font color. If you want to count only non-blank cells you must change the code:

Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double
'Update by Extendoffice
Application.Volatile
Dim rng As Range
For Each rng In pRange1
    If rng.Value <> "" and rng.Font.Color = pRange2.Font.Color Then
        CountColour = CountColour + 1
    End If
Next
End Function

 

[harky]

A change in font color does not indicate a change to a cell, so a function like this must be Volatile. That means it runs any time there is any change in the worksheet, even if the change does not affect font color. So it recalculates a lot. How many cells have this formula?

Font color is a property of a cell. It doesn't matter if the cell is empty, it still has a font color. If you want to count only non-blank cells you must change the code:

Public Function CountColour(pRange1 As Range, pRange2 As Range) As Double
'Update by Extendoffice
Application.Volatile
Dim rng As Range
For Each rng In pRange1
    If rng.Value <> "" and rng.Font.Color = pRange2.Font.Color Then
        CountColour = CountColour + 1
    End If
Next
End Function

more than 5000 row hehe
was thinking possible to run it when i activate it or refreshed after one mass change?? if not it keep counting haha

 

[6StringJazzer]

You can remove Application.Volatile but then you will have to force a manual recalculation (F9) any time you want to ensure that the formula results are current.

 

[harky]

You can remove Application.Volatile but then you will have to force a manual recalculation (F9) any time you want to ensure that the formula results are current.

i try to remove but everytime i change it auto recalculate hehe

 

[harky]

but it ok.. let it be. thx for helping