In a previous post I created a simple weekly schedule with dynamic dates, in this post I am going to highlight hours using date and time ranges, demonstrated in the picture above Here are some random date and time ranges:
Try change date in cell F2 and see how any other week has no highlighted cells (hours).
How to highlight cells in a weekly schedule (conditional formatting)
Conditional formatting does not accept cell references outside current worksheet.
But there is a workaround.
Create a named range for each column and use the named ranges in a conditional formatting formula.
Create named ranges
Select sheet "Time ranges"
Select range B3:B5
Type "Start" in name boxand press Enter
Select range C3:C5
Type "End" in name box and press Enter
Create conditional formatting (Excel 2007)
Select sheet "Weekly schedule"
Select cell range C6:I30
Click "Home" tab on the ribbon
Click "Conditional formatting"
Click "New Rule.."
Click "Use a formula to determine which cells to format"
Type in "Format values where this formula is true" window: