Conditional Formatting

shades · Mar 22nd, 2002, 02:33 PM

I am using a spreadsheet with 15 columns (seven with numerical data, the others are company names, etc.) and 2500+ rows.

I have set up 11 buttons at the top of the spreadsheet for each of the regions (one of the columns). Also, I have included array formula to sum up the totals for the seven numerical columns. So each regional total for each of the seven categories can be seen.

I also have set up conditional formatting, so that when one of the buttons is selected, the corresponding region items will be highlighted in the table. However, I can get only the regional name to highlight, not the entire row.

Is there any way to highlight the entire row istead of just the one column with conditional formatting?

(I'm using Excel 97 - soon to be 2000.)

Mark W. · Mar 22nd, 2002, 02:43 PM

Yes, have the entire row selected when you apply the Conditional Format. Also, use a Formula Is condition.

[ This Message was edited by: Mark W. on 2002-03-22 13:46 ]

shades · Mar 22nd, 2002, 02:46 PM

I tried that. In fact, I had selected the entire table to do that. Then only portions of the table. But still only the one column is highlighted. I must be missing something.

Mark W. · Mar 22nd, 2002, 02:47 PM

Did you use a Formula Is condition? I added that to my 1st reply while you were posting. The cell reference used in the condition must be mixed reference (e.g., $A2).

[ This Message was edited by: Mark W. on 2002-03-22 13:49 ]

[ This Message was edited by: Mark W. on 2002-03-22 13:49 ]

shades · Mar 22nd, 2002, 02:55 PM

Maybe a further explanation. Because there are 11 regions, Excel will not allow more than 7 Nested IF statements in one formula. So, I broke the buttons into six regions for one cell reference (C3)- which then dispalys the region name. Then the other five regions I have linked to another cell reference (C5)- which displays the names. Now, the formulas for the regional summaries are in two different rows (3 and 5).

Anyway, now to the question about the Formula Is, in conditional formatting, do I have to reference both cells C3 C5)? Or are you referrring to something else.

Keep in mind I'm self taught on all this, so I may ask dumb questions. But thanks for hanging in there with me.

Mark W. · Mar 22nd, 2002, 02:57 PM

For example...

A2:B5 contains {1,"A";2,"B";3,"C";4,"D"}. Select A2:B5 (with A2 as the active cell) and apply a "Formula Is" condition with the formula =$A2=4 and a yellow cell pattern. The last row will be highlighted in yellow because its column A value is 4.

[ This Message was edited by: Mark W. on 2002-03-22 14:06 ]

Mark W. · Mar 22nd, 2002, 03:01 PM

What's the condition that your IFs are testing?

shades · Mar 22nd, 2002, 03:06 PM

I was using cell references in the Cell Is. Thus, if C3 = Region 1, then yellow pattern. But because I have another cell reference involved, namely C5, I can't see anyway to make it work.

Here is the formula in C3:

=IF($B$1=1,C8,IF($B$1=2,C9,IF($B$1=3,C10,IF($B$1=4,C11,IF($V$1=5,C12,C13)))))

(I'm using $B$1 as the reference, and C8-C19 is the shortened list of regions)

Now, if you are talking about Formula Is, I'm not sure what reference goes there.

_________________
- old, slow, and confused
... but at least I'm inconsistent -

[ This Message was edited by: shades on 2002-03-22 14:08 ]

Mark W. · Mar 22nd, 2002, 03:12 PM

Quote:

On 2002-03-22 13:57, Mark W. wrote:
For example...

A2:B5 contains {1,"A";2,"B";3,"C";4,"D"}. Select A2:B5 (with A2 as the active cell) and apply a "Formula Is" condition with the formula =$A2=4 and a yellow cell pattern. The last row will be highlighted in yellow because its column A value is 4.

[ This Message was edited
by: Mark W. on 2002-03-22 14:06 ]

Consider this extension to my previous example. Enter 4 into cell D1. Select A2:B5 with cell A2 as the active cell. Change the "Formula Is" condition to =$A2=$D$1. Now you can change the value in D1 (between 1 and 4) and see a different row highlighted.

Do you see how this works? Your buttons should change the content of a cell or the value of a defined name and the conditional format should test for that value.

Mark W. · Mar 22nd, 2002, 03:16 PM

Quote:

On 2002-03-22 14:12, Mark W. wrote:

Quote:

On 2002-03-22 13:57, Mark W. wrote:
For example...

A2:B5 contains {1,"A";2,"B";3,"C";4,"D"}. Select A2:B5 (with A2 as the active cell) and apply a "Formula Is" condition with the formula =$A2=4 and a yellow cell pattern. The last row will be highlighted in yellow because its column A value is 4.

[ This Message was edited
by: Mark W. on 2002-03-22 14:06 ]

Consider this extension to my previous example. Enter 4 into cell D1. Select A2:B5 with cell A2 as the active cell. Change the "Formula Is" condition to =$A2=$D$1. Now you can change the value in D1 (between 1 and 4) and see a different row highlighted.

Do you see how this works? Your buttons should change the content of a cell or the value of a defined name and the conditional format should test for that value.

Why not use the formula, =INDEX(C8:C19,B1), and reference the results of this formula like I did for D1 in the example above?

[ This Message was edited by: Mark W. on 2002-03-22 14:16 ]

Mar 22nd, 2002, 02:33 PM	#1
shades Board Regular Join Date: Mar 2002 Location: Near the Land of Oz Posts: 1,550	I am using a spreadsheet with 15 columns (seven with numerical data, the others are company names, etc.) and 2500+ rows. I have set up 11 buttons at the top of the spreadsheet for each of the regions (one of the columns). Also, I have included array formula to sum up the totals for the seven numerical columns. So each regional total for each of the seven categories can be seen. I also have set up conditional formatting, so that when one of the buttons is selected, the corresponding region items will be highlighted in the table. However, I can get only the regional name to highlight, not the entire row. Is there any way to highlight the entire row istead of just the one column with conditional formatting? (I'm using Excel 97 - soon to be 2000.) __________________ - old, slow, and confused ... but at least I'm inconsistent - (retired Excel 2003 user, 3.28.2008)

Mar 22nd, 2002, 02:46 PM	#3
shades Board Regular Join Date: Mar 2002 Location: Near the Land of Oz Posts: 1,550	I tried that. In fact, I had selected the entire table to do that. Then only portions of the table. But still only the one column is highlighted. I must be missing something. __________________ - old, slow, and confused ... but at least I'm inconsistent - (retired Excel 2003 user, 3.28.2008)

Mar 22nd, 2002, 02:55 PM	#5
shades Board Regular Join Date: Mar 2002 Location: Near the Land of Oz Posts: 1,550	Maybe a further explanation. Because there are 11 regions, Excel will not allow more than 7 Nested IF statements in one formula. So, I broke the buttons into six regions for one cell reference (C3)- which then dispalys the region name. Then the other five regions I have linked to another cell reference (C5)- which displays the names. Now, the formulas for the regional summaries are in two different rows (3 and 5). Anyway, now to the question about the Formula Is, in conditional formatting, do I have to reference both cells C3 C5)? Or are you referrring to something else. Keep in mind I'm self taught on all this, so I may ask dumb questions. But thanks for hanging in there with me. __________________ - old, slow, and confused ... but at least I'm inconsistent - (retired Excel 2003 user, 3.28.2008)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 22nd, 2002, 02:43 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Yes, have the entire row selected when you apply the Conditional Format. Also, use a Formula Is condition. [ This Message was edited by: Mark W. on 2002-03-22 13:46 ]

Mar 22nd, 2002, 02:47 PM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Did you use a Formula Is condition? I added that to my 1st reply while you were posting. The cell reference used in the condition must be mixed reference (e.g., $A2). [ This Message was edited by: Mark W. on 2002-03-22 13:49 ] [ This Message was edited by: Mark W. on 2002-03-22 13:49 ]

Mar 22nd, 2002, 02:57 PM	#6
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	For example... A2:B5 contains {1,"A";2,"B";3,"C";4,"D"}. Select A2:B5 (with A2 as the active cell) and apply a "Formula Is" condition with the formula =$A2=4 and a yellow cell pattern. The last row will be highlighted in yellow because its column A value is 4. [ This Message was edited by: Mark W. on 2002-03-22 14:06 ]

Mar 22nd, 2002, 03:01 PM	#7
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	What's the condition that your IFs are testing?

Mar 22nd, 2002, 03:06 PM	#8
shades Board Regular Join Date: Mar 2002 Location: Near the Land of Oz Posts: 1,550	I was using cell references in the Cell Is. Thus, if C3 = Region 1, then yellow pattern. But because I have another cell reference involved, namely C5, I can't see anyway to make it work. Here is the formula in C3: =IF($B$1=1,C8,IF($B$1=2,C9,IF($B$1=3,C10,IF($B$1=4,C11,IF($V$1=5,C12,C13))))) (I'm using $B$1 as the reference, and C8-C19 is the shortened list of regions) Now, if you are talking about Formula Is, I'm not sure what reference goes there. _________________ - old, slow, and confused ... but at least I'm inconsistent - [ This Message was edited by: shades on 2002-03-22 14:08 ]