Cells Changing Colour To Match Critical Path Dates

[StuckWithExcel]

I've created a Critical Path for a project, shown in the image.

Columns C & D are the Start and End Dates respectively, and I'm keen for the subsequent cells (here F5:AP44) to automatically fill with colour (say, light blue) as the Start & End Dates are updated.

I've seen it done in a previous job but no idea how it's done!

Your help is much appreciated and I hope this can help others with the same question as I've scoured the internet for an answer, but most tutorials seem to be around Conditional Dates relative to today, which is not what I'm after.

Thanks.

 

[JamesCanale]

MrExcelPlayground16.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Project A					4/24/2023	5/1/2023	5/8/2023	5/15/2023	5/22/2023	5/29/2023	6/5/2023	6/12/2023	6/19/2023	6/26/2023	7/3/2023	7/10/2023	7/17/2023	7/24/2023	7/31/2023
2		Task Name	Start	End	Status
3		Scope	4/24/2023	7/12/2023
4		Do a thing	4/24/2023	5/12/2023
5		More Stuff	5/1/2023	5/28/2023
6		Other things	5/15/2023	6/6/2023
7		Good	5/27/2023	7/12/2023
8
9		Technical Elements	5/1/2023	6/30/2023
10		More stuf	5/1/2023	6/1/2023
11		okay	6/2/2023	6/10/2023
12		good	6/11/2023	6/30/2023
13
14		Create Content	6/11/2023	7/15/2023
15		Task 1	6/11/2023	6/15/2023
16		Task 2	6/16/2023	6/30/2023
17		Task3	6/16/2023	7/15/2023
18
19		Write and film	6/30/2023	8/1/2023
20		Go	6/30/2023	7/15/2023
21		And	7/1/2023	7/10/2023
22		Do it	7/16/2023	8/1/2023
Sheet25

Cell Formulas
Range	Formula
G1:T1	G1	=F1+7
C3	C3	=MIN(C4:C8)
D3	D3	=MAX(D4:D8)
C9,C19,C14	C9	=MIN(C10:C13)
D9,D19,D14	D9	=MAX(D10:D13)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:AE22	Expression	=AND($C3<G$1,$D3>=F$1)	text	NO

 

[StuckWithExcel]

MrExcelPlayground16.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Project A					4/24/2023	5/1/2023	5/8/2023	5/15/2023	5/22/2023	5/29/2023	6/5/2023	6/12/2023	6/19/2023	6/26/2023	7/3/2023	7/10/2023	7/17/2023	7/24/2023	7/31/2023
2		Task Name	Start	End	Status
3		Scope	4/24/2023	7/12/2023
4		Do a thing	4/24/2023	5/12/2023
5		More Stuff	5/1/2023	5/28/2023
6		Other things	5/15/2023	6/6/2023
7		Good	5/27/2023	7/12/2023
8
9		Technical Elements	5/1/2023	6/30/2023
10		More stuf	5/1/2023	6/1/2023
11		okay	6/2/2023	6/10/2023
12		good	6/11/2023	6/30/2023
13
14		Create Content	6/11/2023	7/15/2023
15		Task 1	6/11/2023	6/15/2023
16		Task 2	6/16/2023	6/30/2023
17		Task3	6/16/2023	7/15/2023
18
19		Write and film	6/30/2023	8/1/2023
20		Go	6/30/2023	7/15/2023
21		And	7/1/2023	7/10/2023
22		Do it	7/16/2023	8/1/2023
Sheet25

Cell Formulas
Range	Formula
G1:T1	G1	=F1+7
C3	C3	=MIN(C4:C8)
D3	D3	=MAX(D4:D8)
C9,C19,C14	C9	=MIN(C10:C13)
D9,D19,D14	D9	=MAX(D10:D13)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F3:AE22	Expression	=AND($C3<G$1,$D3>=F$1)	text	NO

You are an absolute hero.

Tweaked it to suit my spreadsheet and it worked within 5 mins...I can't thank you enough, you've saved me hours.

Thanks!

 

[JamesCanale]

Happy to be of service. Stick around on this site and you won't be stuckwithexcel, you'll be excelisstuckwithme.