I have a spreadsheet where I have cell within a row that will contain specific text via data validation. So for instance a cell could contain the text "Due in 7 Days", "Past Due", or "Closed".

I want the row color to change depending on the text that appears in the cell. So if the text "Past Due" appears in the cell I want that entire row to turn red, if "Due In 7 Days" appears the row should turn yellow, and if "Closed" the row would turn gray.

2 Answers
2

On the Home tab, click on "Conditional formatting" → "Manage rules..."

Click "New Rule..."

Select "Use a formula to determine which cells to format"

In the formula field, enter the following:
=SEARCH("Due in", $C1) > 0
Instead of C1, use the cell that contains the texts you want to check. But note the $ sign in front of it - that tells Excel to keep the column the same as it expands the formula across the row. That's the trick to applying formatting to an entire row based on a single cell's value.

Click "Format..."

Select the colour you want on the Font tab (if you meant text colour) or the Fill tab (if you meant background colour), as well as any other formatting options you want

Click OK twice

Once back in the Rules Manager, click Apply to see the formatting in effect, and make sure it's how you wanted

Repeat steps 3-8 for the other texts you want to check for, each time selecting the proper colours

Use a conditional format with a formula like =INDIRECT("C"&ROW())="Past Due" and set the background color to red. Add similar rules for the other formats you want to apply. Replace "C" with the actual column holding the values you want to check.