Conditional formatting with an "or" condition

I have a Microsoft® Excel® 2008 for Mac spreadsheet that I use to track the status of work requests. The status values can be any of the following: Most of the requests in the spreadsheet have a status of "Completed". I want to be able to easily identify the ones that are approved, but not yet implemented, ones for which additional information is needed, i.e., "Clarification Required", new ones that have been submitted which are awaiting approval, so are in the "Pending" state, and ones that have been modified after they were completed, so are in the "Modified" state awaiting approval of a modification. I want to color code the background of the cells in the status column for the conditions other than "Completed". But I have 4 conditions and Excel only allows me to specify 3 conditional formatting conditions.

In this case, I want the work requests with a status of "Approved" to have a yellow background and those that have a status of "Clarification" required to have an orange background. I'd like the "Pending" and "Modified" requests to also have an orange background, but a different shade of orange. Since it doesn't matter to me if they share the same background color, I can use an "or" condition when creating the conditional formatting.

I can create the conditional formatting for a particular cell, e.g., B3, by clicking on Format then selecting Conditional Formatting and then specifying the following conditions using the Add button to add additional conditions and clicking on the Format button to select the colors I want for each condition.

Condition 1
Cell Value Isequal to="Approved"
Condition 2
Cell Value Isequal to="Clarification Required"
Condition 3
Formula Is =OR(B3="Pending",B3="Modified")

Conditional Formatting - Or condition

For the last condition, I use "Formula Is" rather than "Cell Value Is", so that I can use the Or operator which won't work if I pick "Cell Value Is". The following steps will then allow me to apply the desired conditional formatting to all cells in the relevant column.

  1. Apply the conditional formatting to a relevant cell then select Edit and Copy to copy that conditional formatting.
  2. Click on the letter at the top of the relevant column to select the entire column. E.g., I would click on "B", if I wanted all of column B selected.
  3. Click on Edit and select Paste Special, select Formats, then click on OK.

    Paste Special - Formats

  4. Click on OK

When the conditional formatting is copied, the relevant cell number will be adjusted for each cell. I.e., B3, which was in the formula I copied, will be replaced with the appropriate cell reference.

With the conditional formatting applied, when I scroll through the list of hundreds of work requests, I can easily identify the ones needing attention.

 

TechRabbit ad 300x250 newegg.com

Justdeals Daily Electronics Deals1x1 px

Valid HTML 4.01 Transitional

Created: Thursday February 26, 2015