Conditional Format ROW based on text in drop-down list

[ava_h]

I'm creating a deal tracking spreadsheet with several columns of customer information. In the last column, I want to have a drop-down list so that the sales team can choose either: Deal Won, Deal Lost, Quoting. In addition, I'd like for the entire row to change colors based on the value they choose in the drop down menu. I know how to create a drop-down list based on data validation, but I'm not sure how to apply the conditional formatting to the entire row based on that specific value.

 

[dave3009]

It depends on your version of Excel, which one are you using? Also which column is the data validation in?

 

[ava_h]

I'm using 2007 and the drop-down list in in column "I". The rest of the columns are just customer info, but I need the entire row to highlight the specific color based on the value of I.

The colors should be something like:
Deals Lost - Red
Deals Won - Green
Quoting - Yellow

Thanks!!

 

[dave3009]

So you'd highlight your range starting the upper most left cell and use - use a formula to determine which cells to format

=$I2="Deals Lost" - set a red format

Then add a new condition for each deal status and set the relevant format.

 

[ava_h]

When I did that, it colors the entire sheet. How can I apply the conditional formatting to each row based on the answers in column I without manually repeating each conditional format? Thanks for your help!

 

[ava_h]

Nevermind, got it working. It was in the details, excel automatically put a "$" before the number of the referenced cell. Thanks again for your help!

 

[Kwillz]

This is exactly what I am trying to do for my worksheet But forgive me I am not understanding how to do what you were instructing to do..... Could you please send me a bit of clarity on how I do this? Thank you so much if you can!!

So you'd highlight your range starting the upper most left cell and use - use a formula to determine which cells to format

=$I2="Deals Lost" - set a red format

Then add a new condition for each deal status and set the relevant format.

 

[dave3009]

Taking the formula apart, I2 holds the value but if you just used I2 the cell to the left would refer to H2, to the right J2 so you use the $ sign to fix I2 that way all cells will refer to it. I said to highlight starting in uppermost left for ease, conditional formatting will apply itself initially this way hwever in Excel 2007 and above you can choose the range that the CF applies to.

Example, you want to highlight a row from columns A through E green based on the value in C being Yes

Excel 2007
	A	B	C	D	E	F
1	Name	Company	Investment	Pension	Savings	CF Formula
2	John	Cars Inc	No	Yes	Yes	=$C2="Yes"
3	Gail	Monsters Inc	Yes	Yes	Yes	=$C2="Yes"
4	Sarah	Toys Inc	Yes	No	No	=$C2="Yes"
5	Mark	Skates Inc	No	Yes	Yes	=$C2="Yes"
6	Joanne	Vans Inc	Yes	Yes	No	=$C2="Yes"
Sheet1

Hope this helps