Passing String to Formula1 Method in VB

[Guest]

I am trying to set conditional formatting to a volitile range of cells. I can
easily evaluate the formula to be pasted into the Conditional format box if
it is not volitile, i.e.

Formula1:="=$E$25=""N"""

However the Row and the Conditional Value are volitle so I can construct the
following string:

Dim MyText As String
MyText = "=$E$" & l.Row & Chr(61) & """""" & r.Cells(n, 15) & """"""

to pass to this statement

With r.Cells(n, 5)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression,
Formula1:=MyText

The formula either evalutes in VB as "TRUE" or "FALSE" before it passes to
the conditional format dilog box or If I add quotes passes the value "MyText"
to the Conditional Format dialog box.

What am I missing here. FYI the entire Macro is context is pasted below.

Sub Conditionals()

Worksheets("Checklist").Range("A26").Activate


Set r = Range("A26:Q574")
For n = 1 To r.Rows.Count
r.Cells(n, 1).Activate


Set c = (r.Cells(n, 14))
If c <> "" Then

With Range("A2:A574")
Set l = .Find(c, LookIn:=xlValues)
If Not l Is Nothing Then
firstAddress = l.Address
Dim MyText As String
MyText = "=$E$" & l.Row & Chr(61) & """""" & r.Cells(n,
15) & """"""
Debug.Print MyText
With r.Cells(n, 5)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression,
Formula1:=MyText _
.FormatConditions(1).Interior.ColorIndex = 0
'Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=$E$25=""N"""

End With

End If
End With
End If
Next n
End Sub

 

[Dave Peterson]

I'm confused over what you want in that CF formula.

Do you want the value of the cell in column O?
Option Explicit
Sub Conditionals()

Dim r As Range
Dim n As Long
Dim c As Range
Dim l As Range
Dim FirstAddress As String
Dim myText As String

Worksheets("Checklist").Range("A26").Activate

Set r = Range("A26:Q574")
For n = 1 To r.Rows.Count
r.Cells(n, 1).Activate

Set c = r.Cells(n, 14)
If c.Value <> "" Then
With Range("A2:A574")
Set l = .Find(c, LookIn:=xlValues)
If Not l Is Nothing Then
FirstAddress = l.Address
myText = "=$E$" & l.Row _
& "=""" & r.Cells(n, 15).Value & """"
Debug.Print myText
With r.Cells(n, 5)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:=myText
'.FormatConditions(1).Interior.ColorIndex = 0
.FormatConditions(1).Interior.ColorIndex = 20
'Selection.FormatConditions.Add Type:=xlExpression, _
' Formula1:="=$E$25=""N"""
End With
End If
End With
End If
Next n
End Sub