Excel – Changing a row’s color depending on the value ( TEXT ) of a cell

1. Copy the column you want to format to an empty worksheet.
2. Select the column, and then choose "Remove Duplicates" from the "Data Tools" panel on the "Data" tab of the ribbon.
3. To the right of your unique list of values or strings, make a unique list of numbers. For instance, if you have 6 categories to color, the second column could just be 1-6. This is your lookup table.
4. In a new column, use VLOOKUP to map the text string to the new color.
5. Apply conditional formatting based on the new numeric column.

Conditional formatting in Excel 2007 (based on value of another cell)

1. Select the cell you want to format, “A1″ in this example.

2. In the Home tab, Styles group, click on Conditional Formatting.

3. Select New Rule.

   

4. The New Formatting Rule window will open.

   

5. Click Use a formula to determine which cells to format.

6. The New Formatting Rule window will now look like this:

   

7. In the edit box labeled “Format values where this formula is true“, enter “=” followed by a formula referencing the other cell. In this example the formula: “=B1<0″ is entered.

8. Click the Format button. The Format Cells window will open.

   

9. Specify the formatting you want. In this example I changed the cell color to “RED”.

10. Click OK to close The Format Cells window.

11. Click OK to close The New Formatting Rule window.

To summarize this example: cell A1 will have a RED color when cell B1 value is less than zero.