Conditional Format a Range Name

[Eric]

How do I set a background color to a range name? This way I can very
easily see the range of the range name. This is important because I
need to keep changing the range of the range name. I am looking for a
way to put a conditional format on a sheet or a workbook that says "if
this cell is part of the range name then set this background color or
text color." Any ideas?

 

[T. Valko]

I need to keep changing the range of the range name

Can you be more specific about that?

You can do this with conditional formatting but you don't want to set
conditional formatting in every cell of the sheet just to identify the named
range. You have to narrow it down and be more specific.

For example, a dynamic range that might span the range A1:B500.

 

[Sheeloo]

Hello Biff,

I am curious to know the solution...
Let us say, I have define a name for the range B1020.
How can I use conditional formatting to format the range A1:F100 such that
only B1020 gets highlighted?

 

[T. Valko]

Defined name = Range = B1020

Create this named formula:

**Select cell A1** This is important!
Insert>Name>Define
Name: InRange
Refers to:

=NOT(ISERROR(Range A1))

OK

Note the space between Range and A1. That is intentional.

Apply the conditional formatting:

Select the range A1:F100
Conditional Formatting
Formula Is: =InRange
Set a nice fill color
OK out

You could also do it by testing the cells row/column numbers to be within
the row/column numbers of the named range but the above method is less
complicated.

 

[Sheeloo]

Thanks Biff,

It worked but I am not able to wrap my head around the idea... how does it
work?

What does Range A1 mean?

 

[T. Valko]

It works based on how Excel accepts and evaluates intersections (in some
cases).

...........A..........B..........C
1.................2008.....2009
2....North......10.........20
3....East.........22........15
4....South.......17........20
5....West........50.......25

If you set it up correctly you can use a lookup formula like this:

=East 2009

The result will be 15.

=NOT(ISERROR(Range A1))

Works on the same principle. If the cell reference is within the
intersection of the named range Range, ISERROR is FALSE and NOT(ISERROR is
TRUE.

You have to give that formula a name and call it like =InRange because it
contains intersections and Excel complains when you attempt to directly use
a conditional formatting formula that contains array constants, unions or
intersections.

 

[Sheeloo]

Thanks Biff,

I have got it now...

It works based on how Excel accepts and evaluates intersections (in some
cases).

...........A..........B..........C
1.................2008.....2009
2....North......10.........20
3....East.........22........15
4....South.......17........20
5....West........50.......25

If you set it up correctly you can use a lookup formula like this:

=East 2009

The result will be 15.

=NOT(ISERROR(Range A1))

Works on the same principle. If the cell reference is within the
intersection of the named range Range, ISERROR is FALSE and NOT(ISERROR is
TRUE.

You have to give that formula a name and call it like =InRange because it
contains intersections and Excel complains when you attempt to directly use
a conditional formatting formula that contains array constants, unions or
intersections.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks Biff,

I have got it now...