Conditional Formatting Excel Spreadsheet Using Access VBA-CodePudding

I am trying to create 2 conditional formats on a spreadsheet I am manipulating using MS Access 365 VBA. I have created the following code to do this:

        Set lrngReport = .Range("$A$2:$" & lstrLastCol & "$" & lintLastRow)
        With lrngReport
        
            .FormatConditions.Delete
            
            'Condition where no record in Donor Comments Modified
            .FormatConditions.Add Type:=xlExpression, Operator:=xlEqual, Formula1:="=OR(ISBLANK($J2),$J2<>'Yes')"
             With .FormatConditions(1)
                .Interior.Color = RGB(255, 243, 109)
                .Font.Bold = True
                .StopIfTrue = False
            End With
        
            'Not reviewed condition
            .FormatConditions.Add Type:=xlExpression, Formula1:="=OR(ISBLANK($L2),$L2<>'Yes'"
            With .FormatConditions(2)
                .Font.Color = RGB(225, 6, 0)
                .Font.Bold = True
                .StopIfTrue = False
            End With
            
        End With

The code fails on 4th line of code

 .FormatConditions.Add Type:=xlExpression, Operator:=xlEqual, Formula1:="=OR(ISBLANK($J2),$J2<>'Yes')"

with error Invalid procedure call or argument.

What am I doing wrong?

CodePudding user response：

Excel won't like the apostrophe delimiters, wants quote marks. Double to escape them as literal text.

Formula1:="=OR(ISBLANK($J2),$J2<>""Yes"")"