Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Relative Conditional Formatting via Macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Relative Conditional Formatting via Macro
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. -- Regards, Tom Ogilvy wrote in message news:neAee.2412$sy6.1511@lakeread04... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Relative Conditional Formatting via Macro
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 " wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Relative Conditional Formatting via Macro
Tom -
Thank you. That's a good bit of insight. ....best, Hash In article , "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy wrote in message news:neAee.2412$sy6.1511@lakeread04... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting based on relative cell value | Excel Worksheet Functions | |||
Conditional Formatting with relative cells | Excel Discussion (Misc queries) | |||
Conditional Formatting Relative to adjacent cells | Excel Discussion (Misc queries) | |||
How do I set conditional formatting relative to another cell -XL20 | Excel Discussion (Misc queries) | |||
Conditional Formatting Bug w/ relative formula? | Excel Programming |