Gantt Chart Conditional Formatting

[ChaosPup]

Hi all,

I'm making a Gantt Chart for a new job, and I'm struggling with the last part of the conditional formatting (thanks to DanteAmor for the earlier solutions).

At the moment, the planned days bar appears in blue based on the value entered in the 'Planned Days' column. If the 'Actual Days' value is greater than the 'Planned Days' value, these extra days are added to the end of the bar in red. This conditional formatting all works fine.

The last part I'm struggling with is - if the 'Actual Days' is LESS than the 'Planned Days', I'd like to 'back-fill' the relevant number of days in green. See below for an example.

In the screenshots below, I've used row 9 as an example and manually changed the colour of the cells so I can explain better.

In the first screenshot, you can see that there is no 'Actual Days' value entered, so the bar only appears in blue. In the second screenshot, I've entered 6 as the 'Actual Days', so the last 6 cells of the bar have changed to green. If I'd entered 10 as the 'Actual Days', the last 2 cells of the bar would have changed to green, and if I'd entered 3 as the 'Actual Days' the last 9 cells of the bar would have changed to green, etc. I hope this explains it OK.

All help welcome! Thanks!

 

[Peter_SSs]

the conditional formatting (thanks to DanteAmor for the earlier solutions).

At the moment, the planned days bar appears in blue based on the value entered in the 'Planned Days' column. If the 'Actual Days' value is greater than the 'Planned Days' value, these extra days are added to the end of the bar in red. This conditional formatting all works fine.

Could you share that Conditional Formatting rule(s)? That may well provide a good starting point for the additional request.

 

[ChaosPup]

Could you share that Conditional Formatting rule(s)? That may well provide a good starting point for the additional request.

Of course, apologies I should have thought of that. See screenshots for the blue and red bars (both of which work perfectly)

 

[Peter_SSs]

See screenshots for the blue and red bars (both of which work perfectly)

Thanks. I've shortened those rules marginally. Also, for the future, please consider giving sample data (including conditional formatting) with XL2BB as we can copy the data rather than having to manually type it out.

See if this does what you want. Check the order of the rules.

23 09 07.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
6								2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
7			Planned	Actual
8
9	2/10/2023	13/10/2023	12	13
10	2/10/2023	13/10/2023
11	2/10/2023	13/10/2023	12
12	2/10/2023	13/10/2023	12	10
13	4/10/2023	12/10/2023	9	12
Gantt

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K9:Y13	Expression	=AND($D9<>"",$D9<=K$6,K$6<=$E9-IF($G9="",0,MAX($F9-$G9,0)))	text	NO
K9:Y13	Expression	=AND($D9<>"",$G9<>"",$G9<$F9,K$6>=$D9+$G9,K$6<=$E9)	text	NO
K9:Y13	Expression	=AND($D9<>"",$D9<=K$6,$D9+$G9-1>=K$6)	text	NO

 

[ChaosPup]

That's it working now, thanks!

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.