Conditional Formatting Bug w/ relative formula?

[Joe HM]

Hello -

I have coded up the following to automatically set the Conditional
Formatting for a cell:

lLine = 28

With Range("D" & lLine )
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$D" & lLine &
"=""X"""
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions.Add Type:=xlExpression, Formula1:="=$D" & lLine &
"=""Y"""
.FormatConditions(2).Interior.ColorIndex = 5
End With

When I run it, the actual Formula is =D37="X" rather than =D28="X". If
I do a Range().Select and then the Selection.FormatConditions.XXX it
works fine. Any idea what that is all about? Am I doing something
wrong here?

Thanks,
Joe

 

[Guest]

Joe
You need to fully quality your range, otherwise your formulas will be
relative to the activecell:

Public Sub test()
lLine = 28
With ActiveSheet
With .Range("D" & lLine)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$D" & lLine & "=""X"""
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions.Add Type:=xlExpression, Formula1:="=$D" & lLine & "=""Y"""
.FormatConditions(2).Interior.ColorIndex = 5
End With
End With
End Sub

cheers
Simon

 

[Guest]

Sorry Joe
Didn't check my last snippet properly
try this:

Public Sub test2()
lLine = 28
With ActiveSheet
With .Range("D" & lLine)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=R" & lLine & "C=""X"""
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions.Add Type:=xlExpression, Formula1:="=R" & lLine & "C=""Y"""
.FormatConditions(2).Interior.ColorIndex = 5
End With
End With
End Sub

It uses R1C1 instead - seems to work
cheers
Simon

 

[Tom Ogilvy]

Since you are using a relative formula, this will be relative to the
activecell. However, in this case, it doesn't appear that you need a
relative formula:

Sub AA()
lLine = 28

With Range("D" & lLine)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$D$" & lLine & "=""X"""
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$D$" & lLine & "=""Y"""
.FormatConditions(2).Interior.ColorIndex = 5
End With

End Sub

works.

 

[Bob Phillips]

Is it because of the activecell. which you then change.

Try this

lLine = 28

With Range("D" & lLine )
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$D$" & lLine &
"=""X"""
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions.Add Type:=xlExpression, Formula1:="=$D$" & lLine &
"=""Y"""
.FormatConditions(2).Interior.ColorIndex = 5
End With


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Joe HM]

Thanks!

I tried that but it gives me relative columns. I need relative rows
and an absolute column. Is there a way to do that?

Joe

 

[Bob Phillips]

Make sure that the correct cell is active when you create the formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Joe HM]

Hello!

Thanks everybody so much for the help! I did activate the cell and now
it's working properly!

Joe