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.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube