View Full Version : [SOLVED:] Adding Conditional Formatting using AND
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
07-28-2022, 10:36 AM
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)")
I still receive an error Run-Time Error 5, Invalid Procedure Call or Argument!:crying:
Paul_Hossler
07-28-2022, 03:00 PM
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
07-28-2022, 03:51 PM
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
That worked, thank you for your help.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.