Conditional Formatting -- Formula Driven

[brunell316]

Hello All,

I am setting up a macro to edit a workbook that uses conditional formatting when it is edited. Here is the code I have set up to set the conditional formatting for said cell:

With Range("P54")
With .FormatConditions
.Delete
.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="SUM(S54,U54,W54,Y54,AA54)"
.Add Type:=xlExpression, Formula1:="AND(S54="",U54="",W54="",Y54="",AA54="")"
End With
.FormatConditions(1).StopIfTrue = False
.FormatConditions(2).StopIfTrue = False
.FormatConditions(2).Interior.ColorIndex = 0
.FormatConditions(1).Interior.Color = 5287936
.FormatConditions(2).SetFirstPriority
End With

The expression/formula Conditional formatting kicks out the expression I have written exactly here with the quotation marks at the end which then makes the formatting expression not work. I have no clue how to get rid of them in the formula so in the conditional formatting screen it reads:

=AND(S54="",U54="",W54="",Y54="",AA54="")

rather than

="AND(S54="",U54="",W54="",Y54="",AA54="")"

I can't seem to figure it out maybe. Thanks for the help.

 

[mikerickson]

Include the = in your formula.

.Formula1:="[COLOR="#FF0000"][B]=[/B][/COLOR]AND(S54="""",U54="",W54="""",Y54="",AA54="""")"

When writing an explicit string, one has to use double quotation marks to create single in the result

Msgbox "this is out ""this is in"" "     '          this is out "this is in"

 

[brunell316]

That did the trick. Thanks a bunch!