Error 13 Type mismatch for Application.Evaluate

[VytautasM]

Good day,

Having trouble making a work project to work. Keep getting Error 13 Type mismatch for Application. Evaluate.

If Application.Evaluate(FC.Formula1) Then
ActiveCondition = Ndx

FC.Formula1 = AND($C8<>"";ISBLANK($AI8))

If I change the conditional formating to a single logic test the code seems to work. But I need this specific formula.

Whole code below:

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

Why am I getting, "Type mismatch" for Application.Evaluate - this thread explains a similar problem, but I do not understand the answer. A clarification of the answer if it applies to my problem would be great (The thread is old did not want to reopen).

Query on Conditional Formatting / Message Box - my initial code comes from here. Thank you contributor.
Conditional Formatting Colors - And originated here.

 

[VytautasM]

I'm trying to recreate it but I cannot produce the same error. Clutching at straws, but is your activesheet the same sheet that has the format condition? If not:

FC.Parent.Parent.Evaluate(FC.Formula1)

Same.

If I change the conditional formating to =ISBLANK(AI8). When I close the doc. everything works as it should and I get the message box I need.

So I am thinking it cannot take into account two cell ranges in the initial conditional formatting = AND($C8<>"";ISBLANK($AI8))

 

[Jon von der Heyden]

So I am thinking it cannot take into account two cell ranges in the initial conditional formatting = AND($C8<>"";ISBLANK($AI8))

I'm sure it can. I think it's either not looking at C8, and that the cell that it is looking at contains an error.

 

[VytautasM]

Since did not manage to figure out a solution, replaced the code with a simple if loop looking for a certain interior color index.

Thanks to everybody who tried to help.

Thread can be closed.

 

[RoryA]

I wonder if it's the semicolon in the formula. What happens if you type that exact formula as a literal string inside an Evaluate call?