How can i change this macro so it uses a cell ref instead of a word?

[tonywatsonhelp]

Hi Everyone,

I have this macro which work great at adding a conditional format to a area, but i can't get it to pull the conditions word from a cell

so instead of "Select Recommendations"
I'd like it to take the words in " Legal.Range("W" & Frow) " (So column W of the selection row)

everything else works but i can't get it to understand the Words, I assume it something to do with the double "" but i'm not sure what i'm supposed to do?

the cell contains just the word no speech marks like = Select Recommendations

any ideas how i can do this?
happy for the formula to be edited and changed, basically it says if cell = Select Recommendations then conditional formatting is true.

so this is the code:

Sub Legal_Con_Format_Row83()
Frow = Selection.Row
Srow = Legal.Range("S" & Frow).Value
Erow = Legal.Range("T" & Frow).Value


Set Cond1 = Legal.Range("E" & Srow & ":H" & Erow)

Cond1.FormatConditions.Delete
With Cond1.FormatConditions _
 .Add(xlExpression, Formula1:="=IF($E" & Srow & "=""Select Recommendations"",TRUE)") [COLOR=rgb(226, 80, 65)]'this is the bit i'm having trouble with![/COLOR]

With .Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 3 'red
End With
With .Font
.Bold = True
.ColorIndex = 3 'red
End With
With .Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.799981688894314 'green
End With
End With
End Sub

 

[jasonb75]

Perhaps change the formula to look at that cell?

.Add(xlExpression, Formula1:="=$E" & Srow & "=W" & Srow)

FWIW you don't need IF in a conditional format rule, the logical test alone is sufficient.

 

[tonywatsonhelp]

Hi jason,
thnks that was nice and quick,
i'll give it a try and see what happens
Tony