Conditional Formatting

shades

Well-known Member
Joined
Mar 20, 2002
Messages
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.)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
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. :(
 
Upvote 0
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
 
Upvote 0
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. :)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.

<font size=-1>[ This Message was edited
by: Mark W. on 2002-03-22 14:06 ]</font>

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.
 
Upvote 0
On 2002-03-22 14:12, Mark W. wrote:
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.<font size=-1>[ This Message was edited
by: Mark W. on 2002-03-22 14:06 ]</font>

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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top