How can I count True Conditional Formating?

[USAMax]

There are so many articles on this and I have spent hours going through them. One returns the color of the Conditional Formatting and is so long just to find if it the conditions have been meet or not!

I found this one that should work but it fails at the Evaluate function.

Function CountCFCells(rng As Range)
Dim i As Single, j As Long
Dim chk As Boolean, Str1 As String, CFCELL As Range
chk = False
For i = 1 To rng.FormatConditions.Count
    Test1 = rng.FormatConditions(i).Interior.ColorIndex
    If rng.FormatConditions(i).Interior.ColorIndex = 65535 Or _
       rng.FormatConditions(i).Interior.ColorIndex = 6 Then
        chk = True
        Exit For
    End If
Next i
j = 0
If chk = True Then
    For Each CFCELL In rng
        If CFCELL.FormatConditions.Count Then
            Str1 = CFCELL.FormatConditions(i).Formula1
            Str1 = Application.ConvertFormula(Str1, xlA1, xlR1C1)
            Str1 = Application.ConvertFormula(Str1, xlR1C1, xlA1, , ActiveCell.Resize(rng.Rows.Count, rng.Columns.Count).Cells(k + 1))
            If Evaluate(Str1) = True Then j = j + 1
            k = k + 1
        End If
    Next CFCELL
Else
CountCFCells = "Color not found"
Exit Function
End If
CountCFCells = j
End Function

The conditional formula is:

=AND(NOT(ISBLANK(RC2)), ISBLANK(RC))

I have always loved Excel but this is pissing me off. Anybody please....

 

[mikerickson]

What is the i in this line? (Inside the If chk = True, For Each CFCELL in rng loop) Should it be a 1?

Str1 = CFCELL.FormatConditions(i).Formula1