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.
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.