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

 

[footoo]

You didn't ask a question.
I'm guessing you need to make this change :

Clr = RngColor.Interior.Color

 

[superfb]

You didn't ask a question.
I'm guessing you need to make this change :

Clr = RngColor.Interior.Color

Apologies - i wanted to count the number of cells colored and the code above wasnt working correctly.....it is now although the number doesnt instantly change when i take the color off....

 

[footoo]

Not exactly what you want but will trigger recalculation of the function when selection is changed :

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

 

[superfb]

Not exactly what you want but will trigger recalculation of the function when selection is changed :

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

Thank you for this.

i keep getting value error

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
    
    Clr = RngColor.Interior.Color
    For Each Cll In Rng
        If Cll.Interior.Color = Clr Then
            CountColor = CountColor + 1
        End If
    Next Cll


End Function

 

[footoo]

Do you mean the function is returning #VALUE !

If so, have you entered in the formula the cell ref containing the color to be counted?

 

[superfb]

Do you mean the function is returning #VALUE !

If so, have you entered in the formula the cell ref containing the color to be counted?

that is correct, the first part of the formula reference the column c, and i want the formula in o2? maybe my interpretation is wrong?

 

[footoo]

If, for examole, the range to count is C1:C10 and the cell with the reference color is N1, put in O2 (or in any other cell) :

=CountColor(C1:C10,N1)

 

[superfb]

Ahhh i see but colm c is the column i want to count how many cells have color........

 

[Fluff]

It is counting col C, N1 can be any cell that has the same colour fill as the cells you want to count.