Conditional formatting on a dynamic range - workaround

  • Thread starter Håkan Björkström
  • 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
 
Ad

Advertisements

G

GS

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top