Why am I getting, "Type mismatch" for Application.Evaluate

[USAMax]

I am working with one of the many codes that will tell me if the Conditional Formatting on a cell is True or False. All of them work until I get to this one line of code.

Dim FC As FormatCondition
If Application.Evaluate(FC.Formula1) Then

Where FC.Formula1 = "=AND(NOT(ISBLANK($B11)), ISBLANK(Q11))"

In this case the Q11 is the active cell. The entire code can be found at: Conditional Formatting Colors


Slightly unrelated, I have been getting unreliable results using the following code so I will take anything at all that will work uning the formula I have used above in FC.Rormula1.

IF Cells(ActiveCell.Row, X).FormatConditions.Count > 0 then

 

[Andrew Poulsom]

You haven't assigned anything to FC.

 

[USAMax]

I am using the code as it is on the link Conditional Formatting Colors, I haven't changed a thing.

The only thing the code requires is a range and I passed the cell's range that has the conditional formatting.

 

[USAMax]

Here is the entire code from the other page. It fails at the, Case xlExpression

Can anyone tell me what Type Application.Evaluate is looking for?

Function ActiveCondition(Rng As Range) As Integer
Dim Ndx As Long
Dim FC As FormatCondition
Dim Temp As Variant
Dim Temp2 As Variant


If Rng.FormatConditions.Count = 0 Then
    ActiveCondition = 0
Else
    For Ndx = 1 To Rng.FormatConditions.Count
        Set FC = Rng.FormatConditions(Ndx)
        Select Case FC.Type
            Case xlCellValue
            Select Case FC.Operator
                Case xlBetween
                    Temp = GetStrippedValue(FC.Formula1)
                    Temp2 = GetStrippedValue(FC.Formula2)
                    If IsNumeric(Temp) Then
                       If CDbl(Rng.Value) >= CDbl(FC.Formula1) And _
                           CDbl(Rng.Value) <= CDbl(FC.Formula2) Then
                           ActiveCondition = Ndx
                           Exit Function
                       End If
                   Else
                      If Rng.Value >= Temp And _
                         Rng.Value <= Temp2 Then
                         ActiveCondition = Ndx
                         Exit Function
                      End If
                   End If

                Case xlGreater
                    Temp = GetStrippedValue(FC.Formula1)
                    If IsNumeric(Temp) Then
                       If CDbl(Rng.Value) > CDbl(FC.Formula1) Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    Else
                       If Rng.Value > Temp Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    End If

                Case xlEqual
                    Temp = GetStrippedValue(FC.Formula1)
                    If IsNumeric(Temp) Then
                       If CDbl(Rng.Value) = CDbl(FC.Formula1) Then
                           ActiveCondition = Ndx
                           Exit Function
                       End If
                    Else
                       If Temp = Rng.Value Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    End If


                Case xlGreaterEqual
                    Temp = GetStrippedValue(FC.Formula1)
                    If IsNumeric(Temp) Then
                       If CDbl(Rng.Value) >= CDbl(FC.Formula1) Then
                           ActiveCondition = Ndx
                           Exit Function
                       End If
                    Else
                       If Rng.Value >= Temp Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    End If

              
                Case xlLess
                    Temp = GetStrippedValue(FC.Formula1)
                    If IsNumeric(Temp) Then
                        If CDbl(Rng.Value) < CDbl(FC.Formula1) Then
                           ActiveCondition = Ndx
                           Exit Function
                        End If
                    Else
                        If Rng.Value < Temp Then
                           ActiveCondition = Ndx
                           Exit Function
                        End If
                    End If

                Case xlLessEqual
                    Temp = GetStrippedValue(FC.Formula1)
                    If IsNumeric(Temp) Then
                       If CDbl(Rng.Value) <= CDbl(FC.Formula1) Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    Else
                       If Rng.Value <= Temp Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    End If


                Case xlNotEqual
                    Temp = GetStrippedValue(FC.Formula1)
                    If IsNumeric(Temp) Then
                       If CDbl(Rng.Value) <> CDbl(FC.Formula1) Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    Else
                       If Temp <> Rng.Value Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    End If

               Case xlNotBetween
                    Temp = GetStrippedValue(FC.Formula1)
                    Temp2 = GetStrippedValue(FC.Formula2)
                    If IsNumeric(Temp) Then
                       If Not (CDbl(Rng.Value) <= CDbl(FC.Formula1)) And _
                          (CDbl(Rng.Value) >= CDbl(FC.Formula2)) Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    Else
                       If Not Rng.Value <= Temp And _
                          Rng.Value >= Temp2 Then
                          ActiveCondition = Ndx
                          Exit Function
                       End If
                    End If
            
               Case Else
                    Debug.Print "UNKNOWN OPERATOR"
           End Select


        Case xlExpression
            If Application.Evaluate(FC.Formula1) Then
               ActiveCondition = Ndx
               Exit Function
            End If

        Case Else
            Debug.Print "UNKNOWN TYPE"
       End Select

    Next Ndx

End If

ActiveCondition = 0



End Function
 

'''''''''''''''''''''''''''''''''''''''


Function ColorIndexOfCF(Rng As Range, _
    Optional OfText As Boolean = False) As Integer

Dim AC As Integer
AC = ActiveCondition(Rng)
If AC = 0 Then
    If OfText = True Then
       ColorIndexOfCF = Rng.Font.ColorIndex
    Else
       ColorIndexOfCF = Rng.Interior.ColorIndex
    End If
Else
    If OfText = True Then
       ColorIndexOfCF = Rng.FormatConditions(AC).Font.ColorIndex
    Else
       ColorIndexOfCF = Rng.FormatConditions(AC).Interior.ColorIndex
    End If
End If

End Function
 

'''''''''''''''''''''''''''''''''''''''


Function ColorOfCF(Rng As Range, Optional OfText As Boolean = False) As Long

Dim AC As Integer
AC = ActiveCondition(Rng)
If AC = 0 Then
    If OfText = True Then
       ColorOfCF = Rng.Font.Color
    Else
       ColorOfCF = Rng.Interior.Color
    End If
Else
    If OfText = True Then
       ColorOfCF = Rng.FormatConditions(AC).Font.Color
    Else
       ColorOfCF = Rng.FormatConditions(AC).Interior.Color
    End If
End If

End Function

'''''''''''''''''''''''''''''''''''''''

Function GetStrippedValue(CF As String) As String
    Dim Temp As String
    If InStr(1, CF, "=", vbTextCompare) Then
       Temp = Mid(CF, 3, Len(CF) - 3)
       If Left(Temp, 1) = "=" Then
           Temp = Mid(Temp, 2)
       End If
    Else
       Temp = CF
    End If
    GetStrippedValue = Temp
End Function
 

'''''''''''''''''''''''''''''''''''''''

Function CountOfCF(InRange As Range, _
    Optional Condition As Integer = -1) As Long
    Dim Count As Long
    Dim Rng As Range
    Dim FCNum As Integer

    For Each Rng In InRange.Cells
        FCNum = ActiveCondition(Rng)
        If FCNum > 0 Then
            If Condition = -1 Or Condition = FCNum Then
                Count = Count + 1
            End If
        End If
    Next Rng
    CountOfCF = Count
End Function

'''''''''''''''''''''''''''''''''''''''

Function SumByCFColorIndex(Rng As Range, CI As Integer) As Double
    Dim R As Range
    Dim Total As Double
    For Each R In Rng.Cells
        If ColorIndexOfCF(R, False) = CI Then
            Total = Total + R.Value
        End If
    Next R
    SumByCFColorIndex = Total
End Function

 

[Andrew Poulsom]

If Q11 contains the CF formula:

=AND(NOT(ISBLANK($B11)), ISBLANK(Q11))

this:

=ActiveCondition(Q11)

returns 1 or 0.

 

[USAMax]

So you are saying that it is working for you?

I rewrote my code in several places to remove the $ in the code and once I removed it I stopped getting the type Mismatch but regardless of the state of Q11 I always get a zero.

I am always testing it with a value in B11 but if Q11 is blank or not the results are zero.

This is the Only thing holding up my project and it is Killing Me.

 

[Andrew Poulsom]

If B11 is not blank and Q11 is blank:

=ActiveCondition(Q11)

returns 1. But the formula won't be recalculated on a change in B11 because that cell isn't a dependent. To force a recalculation you can press Ctrl+Alt+F9.

 

[USAMax]

I do not know what happened to my last message but it is working, sort of.

I found that when I am viewing the sheet in R1C1 it does not work. I use this so I can see the columns as numbers and not letters. But when I have R1C1 turned off it works. It would be nice if you could tell me why it doesn't work with R1C1 turned on and works with it turned off that would be nice but I am just happy it is working now.

 

[Andrew Poulsom]

I imagine it's because the Formula1 property is returning an A1 style formula which can't be evaluated when the setting is for R1C1 reference style.

 

[USAMax]

Now I really feel dumb. I thought Formula1 was just a variable that he had used.

Thank you for all the help and clarification.