3

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?

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

2 Answers 2

Reset to default
6

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.

Improve this answer
3
  • +1, nice use of SEARCH function. I hadn't thought of that.
    – dav
    Commented Sep 26, 2012 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
    Commented Mar 20, 2013 at 12:36
  • Nevermind my last command. The actual problem was in french its RECHERCHE("Due in"; $C1) > 0
    – Rushino
    Commented Mar 20, 2013 at 12:48
2

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged .