Conditional format date range based on drop down menu

[jmpiper]

Okay, this is a little sticky, so I hope I can articulate it properly so that it makes sense. I have a drop-down menu at the top of a worksheet which contains the following values: Q1-2012, Q2-2012, Q3-2012, and Q4-2012. When one of these is selected I would like a cell to search a column of data and find a date that falls within the range indicated by the drop down selection (Example: selecting Q1-2012 would prompt a search for dates between 1/1/2012 and 3/31/2012). The conditional format formula would then highlight all cells within the date range column a certain color. If Q2-2012 is selected from the drop down menu then the conditional format formula would search the column of data and highlight the dates that fall within that specified quarter.

Make sense?

Anyone have experience with this type of conditional formatting?

Many thanks,

Jeff

 

[Marcelo Branco]

A possible solution

Create this table in, say, X1:Z4

X Y Z
[TABLE="width: 173"]
<TBODY>[TR]
[TD="class: xl65, width: 81, bgcolor: transparent"]Q1-2012
[/TD]
[TD="class: xl66, width: 75, bgcolor: transparent, align: right"]01/01/2012
[/TD]
[TD="class: xl66, width: 75, bgcolor: transparent, align: right"]03/31/2012
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Q2-2012
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]04/01/2012
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]06/30/2012
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Q3-2012
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]07/01/2012
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]09/30/2012
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Q4-2012
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10/01/2012
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]12/31/2012
[/TD]
[/TR]
</TBODY>[/TABLE]


A
[TABLE="width: 56"]
<TBODY>[TR]
[TD="class: xl66, width: 75, bgcolor: #c5d9f1"]Quarter
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Q2-2012
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #c5d9f1"]Dates
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]01/01/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]01/11/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]01/21/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]01/31/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]02/10/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]02/20/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]03/01/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]03/11/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]03/21/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]03/31/2012
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]04/10/2012
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]04/20/2012
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]04/30/2012
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]05/10/2012
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]05/20/2012
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]05/30/2012
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]06/09/2012
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]06/19/2012
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: yellow, align: right"]06/29/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]07/09/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]07/19/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]07/29/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]08/08/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]08/18/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]08/28/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]09/07/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]09/17/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]09/27/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]10/07/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]10/17/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]10/27/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]11/06/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]11/16/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]11/26/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12/06/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12/16/2012
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12/26/2012
[/TD]
[/TR]
</TBODY>[/TABLE]


Select A2
Data > Data Validation

Allow: List
Source:$X$1:$X$4


Select all the dates (A5:A41 in the example above) being the first cell (A5 in the example above) the active cell, ie, the one that is not shaded after the selection

Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format

insert this formula
=$A5=MEDIAN($A5,VLOOKUP($A$2,$X$1:$Z$4,2,0),VLOOKUP($A$2,$X$1:$Z$4,3,0))
Format button
Fill --> yellow (for example)

Ok, Ok

Done!

M.