conditional format problem
 

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?
--


Gary

conditional format problem
 

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" <GKeramidasATmsn.com wrote in message
...
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?
--


Gary

conditional format problem
 

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.

--


Gary


"Bob Phillips" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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?
--


Gary

conditional format problem
 

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" <GKeramidasATmsn.com wrote in message
...
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.

--


Gary


"Bob Phillips" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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?
--


Gary

conditional format problem
 

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

--


Gary


"Bob Phillips" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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.

--


Gary


"Bob Phillips" wrote in message
...
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" <GKeramidasATmsn.com wrote in message
...
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?
--


Gary