Named Range in Conditional Formats

[DutchKevin]

Hello All,
Using Excel 2007 I've come accross this issue.
I've got many (10+) conditional formats on a certain range. As the range changes occasionally I need to update each condition manual. I thought to make things easier by creating a named range for this, and use that in the CF.
But somehow that doesn't stick. Excel derectly changes the named range back to it's "nomal" state, with cell references. Why??
Do I miss something? Can I force CF to keep the named range? Or do I need to get into VBA?

Thanks for looking

 

[theBardd]

Are you saying that you have a CF formula of say

=ISNUMBER(MATCH(A4,amt_range,0))

and Excel changes that to

=ISNUMBER(MATCH(A4,$M$1:$M$10,0)

 

[DutchKevin]

Hello Bardd,
It's not quite like that. Let me refrase a bit.
I try to use the named range to define the area on which my CF should work. The actual CF itself works fine, based on dates in one of the first columns.
But the area it applies to is what I'm after.

 

[DutchKevin]

"bump"

 

[DutchKevin]

Hello dear forum,

Last "bump" try.

Also one last refrase to maybe explain better what I mean.

I create a named range of =$A$1:$A$11. Fill it with some letters like a,b,c,d, etc
Call the range "letters"

Now create a new Conditional Format highlighting all letters b.
For the area it applies to I type =letters.
Now all letters b have a format. That's good.
But if you change through Name manager the area of the letters range, any other cells in your bigger range with b do not follow the conditional format.
When checking the CF settings, excel had immediately translated the named range back into the original =$A$1:$A$11

Is it only me who finds this behaviour strange? And does anyone know a workaround, or should I then consider it via VBA?

Kevin

 

[theBardd]

Kevin,

Are you referring to the 'Applies To' field? If you put a name in there, Excel does decompose that to the actual cell address immediately, so you cannot use the name there. If you want to enlarge the range, you need to update that field to encompass the enlarged range.

 

[DutchKevin]

Hi Bardd,

It's indeed the "applies to" field.
There is no workaround for the issue that you know off ??

 

[RoryA]

Use a Table?