Conditional formatting on a dynamic range - workaround

  • Thread starter Thread starter Håkan Björkström
  • Start date Start date
H

Håkan Björkström

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 wouldexpect: 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
 
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
Click to expand...

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
 
Back
Top