Excel VBA Function Not Detecting Cells Colored by Conditional Formatting

Question

I have a few conditional formatting conditions on my excel sheet that fill in cells with different colors. I am trying to count the number of cells colored by my conditional formatting in excel so that I can check their proportions, but for some reason, it keeps returning 0 no matter what. Here is my VBA function:

Function CountCellsByColor(data_range As Range, cell_color As Range)

Dim cell As Range
Dim cnt As Integer
cnt = 0

For Each cell In data_range
    If cell_color.Interior.Color = cell.Interior.Color Then
        cnt = cnt + 1
    End If
Next cell

CountCellsByColor = cnt
End Function

I have 4 different conditions that fill in the colors of the cells, and none of them are detected by this function. When I make the function count white cells over a range, it counts them just fine. Is it just the case that when dealing with conditional formatting, it gives it a "fake" color that is not detectable in this way? Or is there something else going wrong with my code or otherwise?

Thank you so much for the help!

Answer 1

The property Interior.Color only provides the color directly applied to the cell. To access the resulting color from conditional formatting, use the property DisplayFormat.Interior.Color.

Note that this does not work within a user defined function, so you'll have to change your Function into a Sub which writes the result somewhere into your worksheet.

Answer 2

If you want to use this function as UDF you should utilize DisplayFormat property, as stated before, and additionally you should call this function indirectly as a text argument of Evaluate method.

Function CountCellsByColor(data_range As Range, cell_color As Range)

    Dim cell As Range
    Dim cnt As Integer
    cnt = 0

    For Each cell In data_range
        If cell_color.DisplayFormat.Interior.Color = cell.DisplayFormat.Interior.Color Then
            cnt = cnt + 1
        End If
    Next cell

    CountCellsByColor = cnt
End Function

Function ECountCellsByColor(data_range As Range, cell_color As Range)
  '  Application.Volatile   ' if necessary
    ECountCellsByColor = data_range.Parent.Evaluate("CountCellsByColor(" & _
        data_range.Address & "," & cell_color.Address & ")")
End Function