Change color of cell in one worksheet based on value of cell in an

[Dasachtach]

I'm trying to figure out how to write code that will change the color of a
cell in one worksheet when a numerical value in a cell of another worksheet
in the same workbook is added or changed. For example:

I want a cell in a worksheet to be red if a cell value in another worksheet
is < 95%
"" "" "" yellow ""
95% - 97%
"" "" "" green ""

97%

Any ideas?

Mike

 

[Tom Hutchins]

Conditional Formatting doesn't let you reference cells on another sheet (at
least, not in XL 2003). So, pick any out-of-the-way cell in the worksheet
where the conditional formatting will be applied, and put a formula in it to
pull the value from the cell on the other sheet. For example, on Sheet2, cell
AA1, enter:
=Sheet1!B10
You can hide the row or column this cell is in.

Now click on the cell where you want the color to change conditionally.
Select Conditional Formatting from the Format menu. In Condition 1, set
Formula Is to this:
=(AA1<0.95)
Click the Format button, select red on the Patterns tab, and click OK.

Now click Add. In Condition 2, set Formula Is to this:
=(AA1>0.97)
Click the Format button, select green on the Patterns tab, and click OK.

Click Add again. In Condition 3, set Formula Is to this:
=(AA1>0.95)
Click the Format button, select yellow on the Patterns tab, and click OK.
Finally, click OK to close the Conditional Formatting dialog.

Hope this helps,

Hutch

 

[Rick Rothstein \(MVP - VB\)]

You didn't give us specifics, so I'll make up an example to show you how to
do it. Let's say the percentage you want to check are on a worksheet named
Sheet1 in cell D4 and that the cell whose color you want change is E5 on a
worksheet named Sheet2.

First, go to Sheet1 and select D4. Now, click in the Name Box (that is the
edit field on the formula bar to the left of the formula fill-in field) and
type in a name for this range (for this example, call it PercentCell).

Now go to Sheet2, select E5 and then click Format/Conditional Formatting in
Excel's menu bar. Once in there, click the Add button twice so there are 3
conditions showing. For each of the conditions, select Formula Is from their
first drop down boxes.

Now, for Condition 1, put this formula in its 2nd fill-in field...

=PercentCell>97%

then press its Format button and set it to a green color on the Pattern tab;
for Condition 2, put this formula in its 2nd field...

=PercentCell>=95%

then press its Format button and set it to a yellow color on the Pattern
tab; and finally, for Condition 3, pu this formula in its 2nd field...

=PercentCell>0

then press its Format button and set it to a yellow color on the Pattern
tab. Now OK your way back to the worksheet and it should do what you want.

Note: In order to have a reference to a range on a different sheet in
Conditional Formatting, that range must be named and the name must be used
in the formula.

Rick

 

[Dasachtach]

Thanks Rick. I did get as far as naming the range. I couldn't figure out how
to use those names toward my application of the code. I will try it out
Monday.

Thanks

 

[Dasachtach]

Thank you Tom. I will give it a try Monday.

Conditional Formatting doesn't let you reference cells on another sheet (at
least, not in XL 2003). So, pick any out-of-the-way cell in the worksheet
where the conditional formatting will be applied, and put a formula in it to
pull the value from the cell on the other sheet. For example, on Sheet2, cell
AA1, enter:
=Sheet1!B10
You can hide the row or column this cell is in.

Now click on the cell where you want the color to change conditionally.
Select Conditional Formatting from the Format menu. In Condition 1, set
Formula Is to this:
=(AA1<0.95)
Click the Format button, select red on the Patterns tab, and click OK.

Now click Add. In Condition 2, set Formula Is to this:
=(AA1>0.97)
Click the Format button, select green on the Patterns tab, and click OK.

Click Add again. In Condition 3, set Formula Is to this:
=(AA1>0.95)
Click the Format button, select yellow on the Patterns tab, and click OK.
Finally, click OK to close the Conditional Formatting dialog.

Hope this helps,

Hutch

 

[Gord Dibben]

Just a heads-up Tom

If you give the cell(s) on the other sheet a name, you can use the name in your
CF>Formula is.


Gord Dibben MS Excel MVP