Adding Conditional Formatting using AND

[oam]

I am needing to add some Conditional Formatting (CF) using VBA due to users cutting, pasting, and deleting cell and I end up with extra conditional formatting rules, I am trying to use the code below to add a CF to a worksheet but it keeps giving me an error at the formula. The formula works when added into the CF window.

Any and all help would be appreciated.
Thank you

With Sheets("Sheet1").Range("$O$2:$O$500")
        '.FormatConditions.Delete
        With .FormatConditions.Add(Type:=xlExpression, Formula1:="=(AND(E2<>"",A2<>4,A2<>5,A2<>6,A2<>7,$O2<TODAY()+29)")
        .Interior.Color = vbYellow
        .Font.Italic = True
        .Font.Color = vbRed
  '      .Interior.Color = vbRed
  '      .Font.Color = vbWhite
  '      .Font.Bold = True
        .StopIfTrue = False
        End With
                End With

[Paul_Hossler]

Quotes Chr(34) need to be doubled when inside a string

So change

Formula1:="=(AND(E2<>"",A2<>4,A2<>5,A2<>6,A2<>7,$O2<TODAY()+29)")

to something like this and see

Formula1:="=(AND(E2<>"""",A2<>4,A2<>5,A2<>6,A2<>7,$O2<TODAY()+29)")

[oam]

I still receive an error Run-Time Error 5, Invalid Procedure Call or Argument!

[Paul_Hossler]

This seems to work in my test WB

Sub test()
    With Sheets("Sheet1").Range("$O$2:$O$500")
        .FormatConditions.Delete
    
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(E2<>"""", A2<>4,A2<>5,A2<>6,A2<>7,$O2<TODAY()+29)"
        
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            
            .Font.Bold = False
            .Font.Italic = True
            .Font.Color = vbRed
            .Interior.Color = vbYellow
    
            .StopIfTrue = False
        End With
    End With
                
End Sub

I didn't notice it before but I think the parens after .Add and after 29)" were the issue

 With .FormatConditions.Add Type:=xlExpression, Formula1:="=(AND(E2<>"",A2<>4,A2<>5,A2<>6,A2<>7,$O2<TODAY()+29)"

[p45cal]

You don't need the opening bracket in the formula:

With Sheets("Sheet4").Range("$O$2:$O$500")
  '.FormatConditions.Delete
  With .FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(E2<>"""",A2<>4,A2<>5,A2<>6,A2<>7,$O2<TODAY()+29)")
    .Interior.Color = vbYellow
    .Font.Italic = True
    .Font.Color = vbRed
    '      .Interior.Color = vbRed
    '      .Font.Color = vbWhite
    '      .Font.Bold = True
    .StopIfTrue = False
  End With
End With

[oam]

That worked, thank you for your help.