Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Possible Duplicate:
Conditional formatting of TRUE / FALSE values in an Excel 2010 range

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.

How can I do that?

share|improve this question

marked as duplicate by studiohack Sep 26 '12 at 23:34

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

this isn't an exact duplicate of the linked T/F question, though they involve a solution using the same feature. – matt wilkie Oct 9 '12 at 18:02

You can do this with formula-based conditional formatting.

  1. Select the cells you want to apply the formatting to.
  2. On the Home tab, click on "Conditional formatting" → "Manage rules..."
  3. Click "New Rule..."
  4. Select "Use a formula to determine which cells to format"
  5. 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.
  6. Click "Format..."
  7. 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
  8. Click OK twice
  9. Once back in the Rules Manager, click Apply to see the formatting in effect, and make sure it's how you wanted
  10. Repeat steps 3-8 for the other texts you want to check for, each time selecting the proper colours
  11. Click OK or Close to exit the Rules Manager

That should do it.

share|improve this answer
+1, nice use of SEARCH function. I hadn't thought of that. – dav Sep 26 '12 at 19:06
+1 Just a note, on office 2007 you can't put = in the formulea field. So just put something like SEARCH("Due in", $C1) > 0 – Rushino Mar 20 '13 at 12:36
Nevermind my last command. The actual problem was in french its RECHERCHE("Due in"; $C1) > 0 – Rushino Mar 20 '13 at 12:48

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.