Excel 2007 : Conditional Formatting? with Drop-Down List

Hello!

Here's what i have:
Columns A-N will have various information in different formats (Drop down Lists, Numbers, Text, Currency, and Dates). Column M is my "Status" field where i have a drop down list with 7 different values (Paid, Billed, Etc.) that each have a associated color (Red, Green, Etc.).

What i want:
I want to be able to enter all information in Columns A-N and when i select from my "Status" list, for specific cells in the row to turn the associated color.

I.E. - Paid is Red so A2:B2 and D2:N2 would be red when i select paid form the "Status" list. And obviously i would need this to continue indefinitely.

I'm pretty sure conditional formatting wont work but if you have any ideas it would be a great help.


Thank you all!

It would be helpful if you completed your profile by stating which version of Excel you are using as it can affect the answer.

If you are using Excel 2007 you have unlimited Conditional Formatting, but only three conditional formats in prior versions.

If you are using Excel 97-2003, then you will have to use a VBA solution.

So, which version do you have and is VBA an option for you?

I tried playing around with conditional formatting and couldn't get it to work the way i want.

Might be because you have trailing spaces in some of the list items in the drop down. I have removed them in the attached.

See this link for help on understanding Conditional Formatting

Basic CF formula for your worksheet: =$M2="Paid"

Where a CF is created for each of the selections in the list.

Note: other than red for Paid, you did not specify the associated colors for other items in the list, so I made random color choices. Adjust them per your needs.

Darn spaces... that was the issue!

Thanks All!!!