Highlight date ranges in calendar

ankushmehta84 

Activate the Customer sheet.

Select the Arrival Date column of the table and name it (for example) Arrival.

Select the Departure Date column of the table and name it Departure.

Activate the Calender sheet.

Select B10.

Edit the conditional formatting rule.

Change the 'Applies to' range to

=$B$10:$H$15,$J$10:$P$15,$R$10:$X$15,$Z$10:$AF$15,$B$19:$H$24,$J$19:$P$24,$R$19:$X$24,$Z$19:$AF$24,$B$28:$H$33,$J$28:$P$33,$R$28:$X$33,$Z$28:$AF$33

Change the type to 'Use a formula to determine which cells to format', and use

=SUM((Arrival<=B10)*(Departure>=B10))

as formula.

In the attached workbook, the departure date was mostly before the arrival date; I corrected that.