Solved Cell color dependent on another cell's color

May 17, 2012 at 10:35:43
Specs: Windows 7
I would like to change the color of one cell dependent on the color of another cell that has conditional formatting.

cell A11 is a name. Cell I11 is a pay rate with conditional formatting as follows

If I11 is <=9 then cell color format is bold red
if I11 is between 9.01 and 14.00 then the cell color format is bold green
if I11 is >=14.01 then the cell color format is bold blue.

I would like whatever is in A11 to reflect the color that I11 becomes.

Any ideas? Thanks!


See More: Cell color dependent on another cells color

Report •


#1
May 17, 2012 at 11:53:43
Use the same conditions in the Conditional Formatting of cell A11
that you used to color cell I11.

MIKE

http://www.skeptic.com/


Report •

#2
May 17, 2012 at 12:01:39
Mike -

I do not know how to make that work. It does seem simple to me - but the conditions that apply to I11 (to make the font change color) do not apply to the name. When I go to "A11" and try to to conditional formatting - it only sees that cell. I fear I am missing something simple for that I apologize :/

I may not have included all the information above - It is necessary for the Font color to change - not the cell.

I appreciate your response and your advice -
Shelby


Report •

#3
May 17, 2012 at 12:46:55
✔ Best Answer
See if this works for you:

This is for Excel 2007
There are Four separate formulas,
and they must be in the correct order,
so after your done entering all the formulas
they should be, in the following color order:

Blank - No color
Red
Green
Blue

First Formula:

1) Select your first cell Ill
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =ISBLANK(Ill)

6) Click OK
7) Click OK

Second Formula:

1) Select your first cell Ill, should be the same as above.
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =I11<=9

6) Click on the Format button
7) Select the Fill Tab
8) Select a Red color
9) Click OK
10) Click OK

Third Formula:

1) Select your first cell Ill, should be the same as above
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =AND(I11>9,I11<=14)

6) Click on the Format button
7) Select the Fill Tab
8) Select a Green color
9) Click OK
10) Click OK

Fourth Formula:

1) Select your first cell Ill, should be the same as above
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =I11>14

6) Click on the Format button
7) Select the Fill Tab
8) Select a Blue color
9) Click OK
10) Click OK

Now Select Condidional Formatting from the Ribbon,
Select Manage Rules
When the Manage Rules window pops up, on the right side
you will see a column headed "Stop If True"
Check only the box next to the ISBLANK or no color selection.

Now REPEAT the same four formulas,
BUT you will now Select Cell A11
DO NOT change the formulas,
they should still reference cell I11


See how that works.

Just so you know, you do not need to use an =IF() statement.
Conditional Formatting will kick off as long as the Formula returns TRUE.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
May 17, 2012 at 13:10:25
IT WORKED! (which I'm sure you already knew - but was very exciting for me!) I have been looking at this problem that seemed so simple for 2.5 days. I know just enough to be dangerous. I was using "Format cells that only contain" because I couldn't figure out the "formula" that I should use. Thank you - I think my brain is going to be able to rest now. Thank you!

Report •


Ask Question