Gantt Charts & Conditional Formatting

Afternoon all,

I’m trying to automate the process for conditionally formatting a gantt chart with limited success. The organisation I work would prefer a program prepared in Excel rather than Project and I’m concerned at the time it will take to keep updating without this automation!

I’ve attached my current spreadsheet. As you can see from the red conditional formatting, it is not behaving as it should!

In short, I'm trying to achieve automation of the dates on right filled in based on the program start/end dates on the left and would appreciate any help!

Hi,

The easiest way to get this to work is to add another row with individual dates in - let's say in between your month row and your week-date row you add in a new row 5, which contains every date from the start to the end of your project. There's no need for these to be legible - they just need to be there.
Then, select the entire range you want to turn into a Gantt chart (now F9:XK79) and use the following Conditional Formatting formula:
=AND(F$5>=$C9,F$5<=$D9)
This will check if the date in your new row of dates is both >= your start date and <= your end date. Select the red format you want and Robert's your mother's brother.

Here's your file back with that done: Program Template _ Gantt for highguyuk.xlsx

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

I thought that might be the case, but I couldn't get that to work either. See Row 81 where all the dates are in each cell!

Many thanks!!!

You're welcome and thanks for the rep.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.