MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional format based onother cells


Posted by Jim on October 15, 2001 6:51 AM

Without using a macro,is it possible to format a cell based on conditions in other cells? For example, format C1 as yellow background if A1>100 but format C1 as red background if B1>100, else leave C1 formatting as is. Can =IF be used to change formatting?


Posted by Aladin Akyurek on October 15, 2001 7:01 AM

What happens to C1 if


A1>100 and B1>100 at the same time?

Aladin

========

Posted by Jim on October 15, 2001 7:03 AM

Re: What happens to C1 if

In that case, B1 takes precedence so red background.

Posted by Dan on October 15, 2001 7:11 AM

Highlight C1, choose Format-> Conditional Formatting. Under Condition 1, choose Formula Is. In the field to the right type in "=A1>100" (without quotes). Then click the Format button and under the Patterns tab choose yellow color. Then click OK. To add your 2nd condition click on the Add>> button and you will get more fields to specify your other condition. Have fun! HTH

Posted by Dan on October 15, 2001 7:15 AM

Sorry I didn't notice that you had >100 on both conditions. You would want to make the "default" one the first condition, and the secondary one the 2nd condition. That should work. HTH.

Posted by Aladin Akyurek on October 15, 2001 7:59 AM

Jim,

Activate C1.
Activate Format|Conditional Formatting.
Select "Formula Is" for Condition 1.
Enter as formula:

=AND(A1>100,B1<=100)

Activate Format.
Select Yellow on the Patterns tab.
Click OK but don't leave the Conditional Formatting dialog.
Activate Add.
Select "Formula Is" for Condition 2.
Enter as formula:

=B1>100

Activate Format.
Select red on the Patterns tab.
Click OK.
Click OK.

Aladin

==========