Function to Count Cells with a certain background color

Question

Is there a way to count how many cells in a range have a specific background color? I tried the macro below:

Function CountColorIf(rSample As Range, rArea As Range) As Long
    Dim rAreaCell As Range
    Dim lMatchColor As Long
    Dim lCounter As Long

    lMatchColor = rSample.Interior.Color
    For Each rAreaCell In rArea
        If rAreaCell.Interior.Color = lMatchColor Then
            lCounter = lCounter + 1
        End If
    Next rAreaCell
    CountColorIf = lCounter
End Function

 and using =CountColorIF(Reference cell, Range)

But when I enter the formula into excel, a value is not returned. The cell just shows the full formula still. Any help?

Answer 1

If the cell shows the formula the cell is formatted as text. Change the formatting to General and re-enter the formula.

Andreas.