Passing String to Formula1 Method in VB
 

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

Passing String to Formula1 Method in VB
 

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





Stew wrote:

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