Applying conditional formatting

[Diving_Dan]

Morning all,

I'm having a few issues trying to write some code for conditional formatting. I'm going to be adding a loop shortly but want to get this working first.

So I get an error "Expected: end of statement" with the following code for the 2nd line of code.

I know the issue is because of the quotes around "role" but I need the quotes because of the formula. How do I make this work?

Sub add_con_formatting()
    
    Range("h11").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=G11="role""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("C6").Select
    
End Sub

 

[jasonb75]

Try

Formula1:="=G11=""role"""

 

[Diving_Dan]

Perfect thank you

 

[jasonb75]

If I remember correctly, the macro recorder doesn't pick up conditional formatting, but you could record yourself typing the formula into a cell on the worksheet in order to get the correct syntax for any similar problems.

edit:- the macro recorder will give you the cell references in R1C1 notation so you would need to change those, but it will show you the correct duplication of the double quotes where needed.