How can I count the number of cells that are NOT formatted by conditional formatting using Excel VBA?

I am really new at excel vba and I need help with this.

I have a range (A2:A22) in a worksheet ("Numbers") that has been conditionally formatted (Bottom 30%) to have a background fill of a color (ThemeColor = xlThemeColorDark1).

In my column, there are cells that have met the format condition and have a background fill color. The others have no fill.

On a macro I'm trying to build, I want to count the number of cells in the range that has NOT been formatted with the fill.

I've displayed the count in Cell B2, but it's returning 0.

Dim numberRange As Range, r As Long, count As Integer
Set numberRange = Worksheets("Numbers").Range("A2:A22")

count = 0    
For r = 1 To numberRange.Rows.Count
    If Not numberRange(r, 1).FormatConditions(1).In开发者_StackOverflowterior.ThemeColor = _
        xlThemeColorDark1 Then count = count + 1
Next r

Worksheets("Numbers").Range("B2").Value = count

I've looked on forums everywhere, and maybe I'm not seeing the obvious. Please help. Thanks!

I spent a good amount of time on this and managed to create a function that will tell you how many cells in a range that DO or DO NOT satify the conditional formating conditions. This works for numerical conditional formating only (not 'contains text').

• Enter TRUE to count all cells that satify the conditional formating formula (default)
• Enter FALSE to count all cells that DO NOT satify the formula

So in you case you'd do:

=FormatCount(A2:A22, FALSE)

Here is the function!

Function FormatCount(ByVal myRange As Range, _
                     Optional ByVal check_result = True) As Long

Application.ScreenUpdating = False
Dim cell As Range
Dim count As Long
Dim result As Boolean
Dim formula1 As Long
Dim formula2 As Long

For Each cell In myRange
    On Error Resume Next
    With cell.FormatConditions
    If .count = 1 Then
        formula1 = CLng(Right(.Item(1).formula1, Len(.Item(1).formula1) - 1))
        formula2 = CLng(Right(.Item(1).formula2, Len(.Item(1).formula2) - 1))
        Select Case .Item(1).Operator
            Case 1
                If cell.Value >= formula1 And _
                    cell.Value <= formula2 Then
                    result = True
                End If
            Case 2
                If cell.Value < formula1 And _
                    cell.Value > formula2 Then
                    result = True
                End If
            Case 3
                If cell.Value = formula1 Then
                    result = True
                End If
            Case 4
                If cell.Value <> formula1 Then
                    result = True
                End If
            Case 5
                If cell.Value > formula1 Then
                    result = True
                End If
            Case 6
                If cell.Value < formula1 Then
                    result = True
                End If
            Case 7
                If cell.Value >= formula1 Then
                    result = True
                End If
            Case 8
                If cell.Value <= formula1 Then
                    result = True
                End If
        End Select
    End If
    End With

    If result = check_result Then
        count = count + 1
    End If
    result = False
Next

FormatCount = count
Application.ScreenUpdating = True

End Function

How it works:

• First I check if there is 1 conditional format or not (you can tweak this to check all conditions using a for loop and the .count if you really want to).
• The way conditional formatting is stored in excel is with an operator and then the formula. the operator is stored by number (ex. 5 for >) and the formula always starts with "=" even though that isn't part of the formula. So you need to strip the "=" from .formula1 and cast it to a long.
• Then I check the operator of the condition and then use a case select to test the forumula. I then just keep a count of how many cells are a hit (or non-hit).