vba code to count cell color

[superfb]

Hi

I am manually adding a specific color to a cell (Yellow) as a result i would like to count the number of cells that have a color.. i came across this code

Function CountColor(Rng As Range, RngColor As Range) As Integer
    Dim Cll As Range
    Dim Clr As Long
    Clr = RngColor.Range("N1").Interior.Color
    For Each Cll In Rng
        If Cll.Interior.Color = Clr Then
            CountColor = CountColor + 1
        End If
    Next Cll
End Function

 

[superfb]

Hi, thank you for the reply - i am now receiving a name error?????

 

[footoo]

Put the Function code in a normal module (not in a sheet module).

 

[footoo]

Function CountColor(Rng As Range, RngColor As Range) As Integer
    Dim Cll As Range
    Dim Clr As Long
[COLOR=#ff0000]    Application.Volatile[/COLOR]
    Clr = RngColor.Interior.Color
    For Each Cll In Rng
        If Cll.Interior.Color = Clr Then
            CountColor = CountColor + 1
        End If
    Next Cll
End Function

 

[superfb]

I do have it under the module......

ok im not too sure what happen but seems to be working - does take a fair bit of time to calculate

i have the code as follows

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Calculate
End Sub


Function CountColor(Rng As Range, RngColor As Range) As Integer
    Dim Cll As Range
    Dim Clr As Long
    'Application.Volatile
    Clr = RngColor.Interior.Color
    For Each Cll In Rng
        If Cll.Interior.Color = Clr Then
            CountColor = CountColor + 1
        End If
    Next Cll


End Function