Conditional Format for Date Ranges in Calendar

Hi All,

I have a custom annual calendar which I am looking to highlight with scheduled items. For each of these there is and start and end date laid out in columns as shown below.

Item	Start Date	End Date
Party	14 May 2013	15 May 2013
Holiday	20th May 2013	26th May 2013

For a single item I would simply use the following conditional format, with A1 being the cell containing a date:

=AND(A1>=Start_Date,A1<=End_Date)

However for multiple items, I want to use a named range for 'Start_Date' and 'End_Date' so that they all use the same formatting.

Any suggestions?

Thank you!

Oliver

I realise I didn't clarify that all too well.

The name ranges currently only point to 1 cell each:

Start_Date = 14 May 2013
End_Date = 15 May 2013

Now I dont want to do this for every item in my list as I am looking at quite a few.

So I've changed my named ranges to reference the columns:

Start_Dates = A:A
End_Dates = B:B

Now for the conditional format I need to do the same, but check for each row in the named ranges.

Oliver

Calendar Con Format.xlsx

Calendar is attached. Formatting I am referening is in the Calendar tab, D5:AA51, Green filler.

Oliver

That it certainly does! Hadn't thought of using sum product this way, quite an insight.

Greatly appriciated. Thanks!

Oliver