Setting Relative Conditional Formatting via Macro

[Hash]

All --

This is not a question so much about the code for setting conditional
formating. It is more about the approach.

I want to conditionally set H1:H300 to be =SOMECONDTION($H1). I set
(NOT select) the range, delete the format conditions, and then set my
condition. Lots of examples abound about how to do that.

Best explained by example, what happens for me is that the
SOMECONDITION($H1) gets set as for $H1 in what ever row it is that
contains the active cell. In other words, if J3 is the active cell, H3
has SOMECONDITION($H1), H4 has SOMECONDITION($H2) and H2 has
SOMECONDITION($H65536)!

This drove me really nuts until I related it to the active cell. Firmly
taught by the residents here to set vice select, I couldn't find the
straight forward way to do this. I tried .FILLDOWN and a loop thru, in
the end knowing if I _selected_ the range, or at least H1, H1 would be
the active cell and I'd get what I wanted.

In the spirt of M.P.E.P. I ended up setting each cell's condition via a
loop as absolute references--="=SOMECONDITION($H$" & i &")" in shorthand
for the "i"th cell.

What elegance did I miss?

....best, Hash

P.S. If I didn't use absolute column refs, H3 takes SOMECONDITION(J3).
Don't know what would have happened if the active cell was on another
worksheet, but I can guess.

 

[Tom Ogilvy]

I believe the best approach with conditional formatting is to select since
relative addresses are set relative to the active cell.

Most people say: "Selecting is almost never necessary". But sometimes it
is expedient and sometimes it might even be necessary.

 

[Patrick Molloy]

What you want is the format in any cell in H1:H300 to be dependant on what in
column J for the same row.

You'll find this much easier if you switch to R1C1
With Range("H1:H200")
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=RC10=0"
End With
column 10 is J , so the above is th esame as
.FormatConditions.Add Type:=xlExpression, Formula1:="=$J1=0"


when you have $J$1 the ref is R1C1
RCnnn means the row of th ecell with th eformatting, column nnn

 

[Hash]

Tom -

Thank you. That's a good bit of insight.

....best, Hash