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!:crying:

[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.