conditional format problem

[Gary Keramidas]

can anyone explain to me why this happens in this code?

Set rng = Range("B8:f" & tblRows)
With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D8>$D$" & tblRows + 1
End With

tblrows = 31 so the expression equates to =$D8>$D$32

but the equation in row 8 is =$D9>$D$32. each row is off by one.

the operation before this is a copy to f7:f31 and it's still selected, so i
am guessing this is throwing it off

if i do a selection of a single cell, it appears to enter the formula ok.
any way around this without selecting a cell?

 

[Bob Phillips]

Hi Gary,

It is because you have left the row number relative, so Excel (smartly)
updates it for you. If you always want 8, use

"=$D$8>$D$" & tblRows + 1



--

HTH

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

 

[Gary Keramidas]

bob:

therein lies the problem. i don't always want to use D8. i always want to
use D32, which the variable equates to, but i want to use D8 in row 8, D9 in
row 9 and so on.

 

[Bob Phillips]

Gary,

I am not too sure what problem you are experiencing, but it may be that it
doesn't start at 8 because of the activecell. Try this alternative

Set rng = Range("B8:f" & tblRows)
With rng
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D" & ActiveCell.Row & ">$D$" & tblRows
End With



--

HTH

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

 

[Gary Keramidas]

will do, got around it for now by just selecting a cell in row 8.