Vba count cells in a range with a specific color, colored by the Conditional Formatting

Question

I try to count all the cells in a range which have a specific color. In my case the color is green (ColorIndex: 43). The color of the cells is conditioned by the conditional formatting.

My code is up to now as follows:

Function Count_color(range_data As Range, Farbe As Integer) As Integer
    Dim datax As Range
    Dim index As Integer
    For Each datax In range_data
        index = datax.DisplayFormat.Interior.ColorIndex
        If index = Farbe Then
            Count_color = Count_color + 1
        End If
    Next datax
End Function

In the cell I apply the function I get the error message "value". I would be really grateful for your help.

Answer 1

I corrected my code, but it did not answer your question exactly as it did not include conditional formatting.

Corrected code:

Function my_Count_Color(Arg1 As Range, Farbe As Integer) As Integer
    Dim elem As Variant
    For Each elem In Arg1
        If elem.Interior.ColorIndex = Farbe Then
        my_Count_Color = my_Count_Color + 1
        End If
    Next elem
    
End Function

to check, use this formula:

Function GetColor(R As Range) As Integer
    GetColor = R.Interior.ColorIndex
End Function

However, if this solution does not satisfy you, I have a different function proposal for you that should meet your expectations. the only change that has happened is from ColorIndex to RGB. Values corresponding to ColorIndex = 43 = RGB (146,208,80). I substituted them as optional into the formula, or rather 2. and now you can type My_Count_Color_2 = (G1: G10) or My_Count_Color_2 = (G1: G10; 146; 208; 80)

Here is my code:

Function my_Count_Color_2(rng As Range, Optional R As Integer = 146, Optional G As Integer = 208, Optional B As Integer = 80) As Integer
    Dim elem As Variant
        For Each elem In rng
                If (rng.Parent.Evaluate("DFColor(""" & elem.Address & """)") = RGB(R, G, B)) = True Then
                    my_Count_Color_2 = my_Count_Color_2 + 1
                End If
        Next elem
    
End Function

Public Function DFColor(addr)
    DFColor = Range(addr).DisplayFormat.Interior.Color
End Function