Color row

sccson

New Member
Joined
Oct 31, 2005
Messages
20
Hello,

Is there a way to color an entire row based on the color of the cell next to it?

I used conditional formatting on a cell based on a value, but I also need to color the cell next to it (it does not have a value), the cell has a name.

Name Total

John Smith 40

Based on the total the entire row should be
Red. The numbers - 0 - 4 green, 5 to 8
yellow, 5 to 12 orange 13 and up is red





Thank you
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

cfree36

Board Regular
Joined
Oct 5, 2005
Messages
175
You can use the same conditional format using formula is.. just apply it to all cells in the row

EXAMPLE:

Conditional Format

Formula is:
=$A1=40

Apply that conditional format to all cells in row 1.
If A1 = 40 the entire row will format as you specify.
 

sccson

New Member
Joined
Oct 31, 2005
Messages
20
I see, the problem is that the "name cell already has a formula (=Sheet1!B1). How can add to it?

Also, the number is always a variable.

Thank you
 

cfree36

Board Regular
Joined
Oct 5, 2005
Messages
175
The only thing you are changing in the name column is the conditional formats.... not the formula in the cell.

You can enter three 'Conditions'

Formula is: =$B1<=4 format green
Formula is: =AND($B1>=5,$B1<=8) format yellow
Formula is: =$B1>=13 format orange

By locking the B you can copy that format to all cells in your sheet and the conditional format will work for the entire row based on the value of what is in column B.
 

sccson

New Member
Joined
Oct 31, 2005
Messages
20

ADVERTISEMENT

It's been a long day. I don't understand how to add / enter formula

Sorry
 

cfree36

Board Regular
Joined
Oct 5, 2005
Messages
175
Highlight the range where you want the rows colored according to the data in column B (totals). A1:D236 (or whatever).

Then goto Format on the toolbar / Conditional Formatting.
Change the drop down box that currently says "Cell Value Is" to "Formula Is"
In the box to the right type this formula:
=$B1<=4
Choose the format you want for totals less than or equal to 4.
Click Add
Do the above for Condition 2 using this formula:
=AND($B1>=5,$B1<=8)
Choose the format you want for totals greater than or equal to 5 and less than or equal to 8.
Click Add
Do the above for Condition 3 using this formula:
=$B1>=13
Choose the format you want for totals greater than or equal to 13.

You are entering the formula in the conditional format... not on the worksheet. By choosing an entire range and only locking '$' the Column in the conditional format formula... each cell in the row will look to the total to see what color it should be.
 

sccson

New Member
Joined
Oct 31, 2005
Messages
20
Thank you, for the help. I was having problems with it working properly at first, but after I added =IF in the first condition, it seems to work.

Thanks again for the help.

Mike
 

cfree36

Board Regular
Joined
Oct 5, 2005
Messages
175
However you shouldn't need the 'IF' in a conditional format as it is implied.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,987
Messages
5,834,722
Members
430,313
Latest member
smartykatwinks

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
Top