Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting on a dynamic range - workaround
If you are trying to apply conditional formatting to a dynamic named range, you will find (anyway in Excel 2007) that the result is not what you would expect: In CF, when you enter a range name - say "MyRange" into the "Applies to", Excel will change it to the absolute address. Adding a row to the range will automatically expand the named range, but, CF still applies to the absolute address you entered into the CF range.
Workaround: Record the conditional format settings to a macro. In the macro, Excel will first delete previous formatting, after that it selects the range to apply to: "Range($A$1:$B$18).Select". Change the absolute address to your range name "Range("MyRange").Select". Change the formatting commands according to your needs. Put all formatting steps into the same routine. Remember to preserve "deleting format" once only, at the very beginning of the routine. If you have a smarter solution, please share! Good luck Håkan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting on a dynamic range - workaround
If you are trying to apply conditional formatting to a dynamic named
range, you will find (anyway in Excel 2007) that the result is not what you would expect: In CF, when you enter a range name - say "MyRange" into the "Applies to", Excel will change it to the absolute address. Adding a row to the range will automatically expand the named range, but, CF still applies to the absolute address you entered into the CF range. Workaround: Record the conditional format settings to a macro. In the macro, Excel will first delete previous formatting, after that it selects the range to apply to: "Range($A$1:$B$18).Select". Change the absolute address to your range name "Range("MyRange").Select". Change the formatting commands according to your needs. Put all formatting steps into the same routine. Remember to preserve "deleting format" once only, at the very beginning of the routine. If you have a smarter solution, please share! Good luck Håkan A 'dynamic' range is the result of a formula. Try prepending a equal sign to the name range in the 'Applies to'... =MyRange -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Problem - Dynamic Formula?? | Excel Worksheet Functions | |||
Applying conditional formatting to dynamic range | Excel Discussion (Misc queries) | |||
Dynamic Conditional formatting | Excel Programming | |||
Conditional Formatting & Dynamic Range | Excel Worksheet Functions | |||
Dynamic conditional formatting | Excel Programming |