Colour cells based on date ranges

Hi,

I would like to know if it's possible to colour cells based on a date range entered in start date and end date cells.

My spread sheet (attached) has start date and end date cells and to the right of these each column is headed with the w/c date. My requirement is to enter a start date (for example 21/01/2019) and an end date (for example 22/04/2019) and for the cells that correspond to these dates to the right change colour to show a time line of activity.

Is this possible?

Was thinking conditional formatting with a formula to indicate the affected cells but unable to get close to the requirement.

Any help would be appreciated.

Try this:

Highlight F7:BF17 > Conditional Formatting > New Rule > Use a formula
=AND(F$4>=$D7,F$4<=$E7)
Format: Fill color of your choice > OK > OK

You can use the same idea for Compliance Testing and Reactive Maintenance.

Perfect, works a treat. Thank you loads.

Sorry I have spotted an issue, nothing major, but if you could help again that would be great.

I’ve just tested a few dates and the only thing I can see is if the project start date is in one of the weeks but is not the start date for that week, the conditional filling fills the next cell along. E.g. D7: 16/01/2019 & E7: 06/03/2019 – H7-O7 should auto-fill however, I7-O7 fill instead.

Is this easy to resolve?

Really appreciate it. Both these solutions work perfectly, amazing effort guys.