Apply Conditional Formatting via VBA Fails Expected end of Statement

[detriez]

With help from the forum I got this conditional formatting work.

=IFERROR(VLOOKUP(H2,'Standard Values'!D:G,4,0)="MQL",FALSE)

I need to move it to VBA but, it fails at "MQL" with Compile error: Expected End of statement

    Range("H2:H250000").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(H2<>"""",=IFERROR(VLOOKUP(H2,'Standard Values'!D:G,4,0)="MQL",FALSE)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = vbBlue
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

 

[rhombus4]

In VBA if a formula has quotation marks inside it you need to add extra ones

"" Will need to be """"
" Will need to be ""

So "MQL" needs to be ""MQL""

 

[mohadin]

Try

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(H2<>"""",IF(VLOOKUP(H2,'Standard Values'!D:G,4,0)=""MQL"",TRUE,FALSE))"

 

[detriez]

Thanks all
Removing the first = and adding double quotes did the trick.

I Appreciate the help

 

[mohadin]

You are welcome
And thank you for the feedback
Be happy & safe