Conditional formatting with multiple rules in Excel 2007

I have two previous posts on Excel 2007 conditional formatting: One based on cell’s own value and the second based on value of another cell. This post will cover conditional formatting with multiple rules or conditions.

Suppose you have a cell formatted with this rule:

If cell value is less than zero format with Light Red Fill …. Etc.

xl7cfrule1

Now you want to add another rule:

If cell value is greater than 100 format with green Fill.

Follow these steps:

1. In the Home Tab, Styles group click Conditional Formatting, then Manage Rules.

xl7cfrule2

2. The window Conditional Formatting Rules Manager will open, and it will show the current rule.

xl7cfrule3

3. Click the New Rule button. The New Formatting Rule window will open (not shown here).

4. Add the new rule. If you need help refer to the other two posts I mentioned earlier.

5. After finishing, your list of rules will look like this:

xl7cfrule4

Note:

You can apply the same steps if you have conditional formatting based on the value of another cell.

Comments

  1. DyingIsis says:

    This is probably easy for everyone except for me.

    I have a table with multiple column that need to be filled out.

    I can enter the same 3 options into each column (N/A, Yes, No).

    If I enter “N/A” into A1, I would like to make sure that B1, C1, D1, etc also say “N/A”.

    If I accidentally enter “Yes” into B1 when I put “N/A” in A1, I would like B1 to be highlighted in red.

    I only care for the case when Column A equals “N/A”.

    Please help.

    Thanks for your time.

  2. admin says:

    Q1. If I enter “N/A” into A1, I would like to make sure that B1, C1, D1, etc also say “N/A”.

    A1. Enter this formula in B1:

    =IF($A$1=”N/A”;”N/A”;”")

    Then drag to the right to Autofil C1 and D1.

    Q2.If I accidentally enter “Yes” into B1 when I put “N/A” in A1, I would like B1 to be highlighted in red.

    A2. Use conditional formatting based on another cell, create a new rule using this formula:

    =AND(A1=”N/A”;B1=”YES”)

    in conditional formatting specify fill with red color for.

    See this link:

    http://www.exceldigest.com/myblog/2009/04/16/conditional-formatting-in-excel-2007-based-on-value-of-another-cell/