| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format a Range Name
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Format a Range Name
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. -- Biff Microsoft Excel MVP "Eric" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Format a Range Name
Hello Biff,
I am curious to know the solution... Let us say, I have define a name for the range B10:D20. How can I use conditional formatting to format the range A1:F100 such that only B10:D20 gets highlighted? "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Eric" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Format a Range Name
Defined name = Range = B10:D20
Create this named formula: **Select cell A1** This is important! InsertNameDefine 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. -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... Hello Biff, I am curious to know the solution... Let us say, I have define a name for the range B10:D20. How can I use conditional formatting to format the range A1:F100 such that only B10:D20 gets highlighted? "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Eric" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Format a Range Name
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" wrote: Defined name = Range = B10:D20 Create this named formula: **Select cell A1** This is important! InsertNameDefine 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. -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... Hello Biff, I am curious to know the solution... Let us say, I have define a name for the range B10:D20. How can I use conditional formatting to format the range A1:F100 such that only B10:D20 gets highlighted? "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Eric" wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Format a Range Name
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. -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... 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" wrote: Defined name = Range = B10:D20 Create this named formula: **Select cell A1** This is important! InsertNameDefine 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. -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... Hello Biff, I am curious to know the solution... Let us say, I have define a name for the range B10:D20. How can I use conditional formatting to format the range A1:F100 such that only B10:D20 gets highlighted? "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Eric" wrote in message ... 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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Format a Range Name
Thanks Biff,
I have got it now... "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... 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" wrote: Defined name = Range = B10:D20 Create this named formula: **Select cell A1** This is important! InsertNameDefine 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. -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... Hello Biff, I am curious to know the solution... Let us say, I have define a name for the range B10:D20. How can I use conditional formatting to format the range A1:F100 such that only B10:D20 gets highlighted? "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Eric" wrote in message ... 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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional Format a Range Name
You're welcome!
-- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... Thanks Biff, I have got it now... "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... 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" wrote: Defined name = Range = B10:D20 Create this named formula: **Select cell A1** This is important! InsertNameDefine 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. -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... Hello Biff, I am curious to know the solution... Let us say, I have define a name for the range B10:D20. How can I use conditional formatting to format the range A1:F100 such that only B10:D20 gets highlighted? "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Eric" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional format of range | Excel Discussion (Misc queries) | |||
| Conditional Format over Date Range | Excel Discussion (Misc queries) | |||
| conditional format a range | Excel Discussion (Misc queries) | |||
| Conditional Format based on range | Excel Discussion (Misc queries) | |||
| Conditional format IP range | Excel Discussion (Misc queries) | |||
![Eric[_4_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
