March 19th, 2010 
My Health 
Powered by Max Banner Ads
Here is what i’m trying to do:
I have a row of data for the members of a fitness club including name, number, city, etc. The final column then asks if they are currently a member or not using a “Yes”, “No”, or “Pending” drop down list.
I want to create a formula that checks the drop down box and if “Yes” is checked it sets the entire row to a certain background color (ie: light green), if “No” is checked then it sets the background of the entire row to red, and if “pending” is checked it sets it to light blue.
So, to summarize:
I want to set the color of an entire row depending on the value of a specific drop down menu (3 possibilities).
I got it to color the drop down menu cell a specific color depending on the result using condition formatter, but i cant figure out how to get the whole row a color. I’m thinking ill just have to create a formula on another page of my workbook and have it reference that cell using a lookup function and then execute an action depending.
Thank you for any help
Posted in 
Tags: 
pretty sure that changing background color like that requires a visual basic marco
I would just sort by yes no pending and manually change the background color on multiple rows at once.
What you want is called “Conditional Formatting”. This straight from the Micro$oft Excel “Help” section.
“Add a conditional format”
Do one of the following:
To use values in the selected cells as the formatting criteria, click Cell Value Is, select the comparison phrase, and then type a constant value or a formula. If you enter a formula, start it with an equal sign (=).
To use a formula as the formatting criteria (to evaluate data or a condition other than the values in selected cells), click Formula Is and then enter the formula that evaluates to a logical value of TRUE or FALSE.
Click Format.
Select the formatting you want to apply when the cell value meets the condition or the formula returns the value TRUE.
To add another condition, click Add, and then repeat steps 1 through 3.
You can specify up to three conditions. If none of the specified conditions are true, the cells keep their existing formats.
Note Using multiple conditions If more than one specified condition is true, Microsoft Excel applies only the formats of the first true condition, even if more than one condition is true.
If column F has your drop-down lists
And all your data is in A2 to F50
Select all your cells from A2 to F50
Select from the menu Format Conditional Formatting
Condition 1:
Formula is =$F2=”Yes”
Set a background format a color for yes
Condition 2:
Formula is =$F2=”No”
Set a background format a color for no
Condition 3:
Formula is =$F2=”Pending”
Set a background format a color for pending
OK
Notes
Excel will change the formulas for each row e.g. F3, F4, F5 etc.
The $ sign keeps all the formulas in a given row to reference column F
$F2 has to be the first row of all the selected rows. If say cell F3 is the first row, change it to $F3