Author: Oscar Cronquist Article last updated on December 23, 2018

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

  1. Select sheet "Time ranges"
  2. Select range B3:B5
  3. Type "Start" in name boxand press Enter
  4. Select range C3:C5
  5. Type "End" in name box and press Enter

Create conditional formatting (Excel 2007)

  1. Select sheet "Weekly schedule"
  2. Select cell range C6:I30
  3. Press with left mouse button on "Home" tab on the ribbon
  4. Press with left mouse button on "Conditional formatting"
  5. Press with left mouse button on "New Rule.."
  6. Press with left mouse button on "Use a formula to determine which cells to format"
  7. Type in "Format values where this formula is true" window:
    =SUMPRODUCT((C6>=Start)*(C6<End))
  8. Press with left mouse button on "Format..." button
  9. Select "Fill" tab
  10. Select a color
  11. Press with left mouse button on Ok
  12. Press with left mouse button on Ok
  13. Press with left mouse button on Ok