Excel 2007 : Mutiple conditional formatting based on a drop down menu

**lolweb** · 02-09-2011, 08:55 AM

Hi there,

I am no expert in Excel and there must be a really simple solution to my problem.

Basically, I have a drop down menu with 2 options. Depending on which option is selected I need a set of conditional formatting rules to apply to a separate cell.
So in this separate cell I will have a numerical value which needs to be assigned a colour based not only on its numerical value but also which option is selected in the drop down menu.

I hope that makes sense

**NBVC** · 02-09-2011, 09:00 AM

Select Use a formula to determine which cells to format and then use a formula like:

=AND($A1=choice,$B1=your_numeric_condition)

where A1 contains the drop down and choice represents the option picked... and B1 is the cell you are colouring with the number and your_numeric_condition is the condition to apply to that number.

**lolweb** · 02-10-2011, 05:10 AM

Thank you for responding so quickly!

So if I want the numerical value in B1 to appear green if it is below 600 and blue if it is above 600...what would the formula be?

=AND($A1=choice,$B1=???)

**NBVC** · 02-10-2011, 08:43 AM

You need to split that into 2 conditions.

For Green:

=AND($A1="choice",$B1>600)

notice that if the choice made in A1 is a text string you need to put it in quotes...

then you add new rule and use formula

=AND($A1="choice",$B1<600) and choose Blue.

Note: What if B1 is equal to 600... you can change either of the above to include 600 by adding an equal to the > or < , e.g $B1>=600 or $B1<=600