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 |
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 |
All times are GMT +1. The time now is 12:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com