» Coloring Based on Text Criteria in Excel 2007

To color based on text criteria:

1. Copy a column containing a list of items such as Customer Name, see column C, and paste it to a new column.
2. Select the new column, select Data -> Remove Duplicates (in Data Tools Group).
3. Select a cell in the List and press Ctrl+Shift+* or press Ctrl+A to select the List.
4. Select Home -> Conditional Formatting (in Styles Group) -> Highlights Cells Rules -> Text That Contains.
5. In Format cells that contain the text select a cell in the column where you operate the Remove Duplicates, in this example (see screenshot) select cell $H$2, repeat and select more cells from the new List while selecting from with dropdown list a new formatting and click OK.



To color rows based on text criteria:

1. Select a cell in the List and press Ctrl+Shift+* or press Ctrl+A.
2. Select Home -> Conditional Formatting (in Styles Group) -> New Rule.
3. Select Use a Formula to determine which cells to format.
4. In Format values where this formula is true enter the formula =$D1=$H$2, click the Format button, select the Fill tab, choose a color and click OK, repeat and add more rules.

Note:
Be sure to distinguish between Absolute reference and Relative reference when entering the formulas.