Conditional Formatting on Drop Down List

Closed
Mar - May 27, 2010 at 02:51 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 11, 2013 at 11:54 AM
Hello,

Using Excel.

The Data in column L is populated by a dropdown list of roughly 15 options, all of which are text. When certain options are selected in column L, I'd like the entire row to turn yellow. I can't seem to figure out how to set up my conditional formatting to make this happen. Can anybody help?

2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 27, 2010 at 03:44 PM
Select the rows on which you want to have this formatting.
Let say you want from row 5 to row 55 and the triggering cell is D


Select rows 5:55
goto conditional formatting
enter the formula

=$D5<>""

choose the color by clicking on format in conditional format


$D is saying that trigger is cell D
and 5 is the first row of the selection
formula is saying color when there is a value in d of a row
3
I'm not sure I understand what a trigger cell is. Let me define more clearly what I want so you can determine if your suggestion is appropriate. If it is, please provide more explination (I'm not an advanced Excel user). Thanks!!

1) i'm not sure what a trigger cell is
2) the "list" of options that the user can pick in the dropdown is hidden in cells AA6-AA21.
3) Some of those options are "good" and some are "bad.
4) What I'm tyring to accomplish is: if the user picks a "bad" option I want the entire row to turn yellow. If the user chooses a "good" option I want the row to remain without color.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 27, 2010 at 04:42 PM
By trigger cell I meant to say that in general, one would say some thing like this
if there is a data in cell in Column D, then turn that row red or if the value in cell C2 is yellow, then turn column C yellow. By trigger I meant the cell that will cause that event to occur.

Lets say that user will be picking up the values in column F based on the list defined in range AA6-AA21

so now you select the rows on which you want this change of color to occur. Lets say its whole sheet. In this case you select all rows

Now go to conditional formatting
and enter this formula

=OR($F1="bad value1", $F1="bad value2", $F1="bad value 3", "What a day")
and choose color yellow

Now if any cell in F column is "Bad Value1" or "bad value2" or "bad value 3", or "What a day" the whole row will turn yellow.
I Just had four values, but you can have more

Hope it make things better
0
How to do the same function as above for a range
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 25, 2012 at 04:52 PM
@ABC, what you meant for a range. Color the range or being triggered by a range
0
Is there no way to get it to determine whether the value of the cell is one of the values in a defined list? It seems a shame to have to go to all that trouble to name the list so you can call it for use in a drop-down, but then not be able to use that call for formulae.
0
you cant you will need a macro for that
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 27, 2010 at 03:46 PM
Only if you need more than three conditions or the formula cannot be written on sheet and needs some user defined function
0