Auto color gantt chart

[haziqFARHAN]

Hi I have a problem. the colour is fill automatically by formula referring "maintenance start date" and "maintenance complete date'.
How to combine the colour merge refer "name" ?

an example if the name is "AFD" have 4x then the colour is just fill in one row only. no need to fill 4 rows.
i stuck here. hope can help

 

[JamesCanale]

On the bottom, I left the formulas spread out a bit below to make it easier to understand.

MrExcelPlayground5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1	Name	Machine	Start	Finish	Thing	Item	Date	Start	Finish	6:55	7:35	8:05	8:35	9:05	9:35	10:05	10:35	11:05	11:35	12:05	12:35	13:05	13:35	14:05	14:35	15:05	15:35	16:05	16:35	17:05	17:35	18:05
2	AFD	916	3/4/2021 7:08	3/4/2021 12:42	B	15A	3/4/2021	7:08:00 AM	12:42:00 PM	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE
3	AFD	CCR10	3/4/2021 8:56	3/4/2021 12:15	B	15A	3/4/2021	8:56:00 AM	12:15:00 PM	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
4	AFD	923	3/4/2021 12:31	3/4/2021 13:58	B	15A	3/4/2021	12:31:00 PM	1:58:00 PM	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
5	AFD	CCR10	3/4/2021 16:55	3/4/2021 17:28	B	15A	3/4/2021	4:55:00 PM	5:28:00 PM	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
6	AH	F161	3/4/2021 9:55	3/4/2021 12:39	B	18A	3/4/2021	9:55:00 AM	12:39:00 PM	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
7	AH	F019	3/4/2021 10:59	3/4/2021 19:57	B	18A	3/4/2021	10:59:00 AM	7:57:00 PM	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
8	AMS	F010	3/4/2021 7:23	3/4/2021 7:50	B	18A	3/4/2021	7:23:00 AM	7:50:00 AM	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
9
10
11	Name	Machine	Start	Finish	Thing	Item	Date	Start	Finish	6:55	7:35	8:05	8:35	9:05	9:35	10:05	10:35	11:05	11:35	12:05	12:35	13:05	13:35	14:05	14:35	15:05	15:35	16:05	16:35	17:05	17:35	18:05
12	AFD	916	3/4/2021 7:08	3/4/2021 12:42	B	15A	3/4/2021	7:08:00 AM	12:42:00 PM	0	7:08:00 AM	12:42:00 PM		TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
13	AFD	CCR10	3/4/2021 8:56	3/4/2021 12:15	B	15A	3/4/2021	8:56:00 AM	12:15:00 PM		8:56:00 AM	12:15:00 PM
14	AFD	923	3/4/2021 12:31	3/4/2021 13:58	B	15A	3/4/2021	12:31:00 PM	1:58:00 PM		12:31:00 PM	1:58:00 PM
15	AFD	CCR10	3/4/2021 16:55	3/4/2021 17:28	B	15A	3/4/2021	4:55:00 PM	5:28:00 PM		4:55:00 PM	5:28:00 PM
16	AH	F161	3/4/2021 9:55	3/4/2021 12:39	B	18A	3/4/2021	9:55:00 AM	12:39:00 PM
17	AH	F019	3/4/2021 10:59	3/4/2021 19:57	B	18A	3/4/2021	10:59:00 AM	7:57:00 PM
18	AMS	F010	3/4/2021 7:23	3/4/2021 7:50	B	18A	3/4/2021	7:23:00 AM	7:50:00 AM
Sheet12

Cell Formulas
Range	Formula
K1,K11	K1	=J1+40/60/24
L1:AF1,L11:AF11	L1	=K1+30/60/24
G2:G8,G12:G18	G2	=INT(C2)
H2:I8,H12:I18	H2	=MOD(C2,1)
J2:J8	J2	=IF($A2<>$A1,SUM((IFERROR(INDEX($H$2:$H$8,1/(1/((INDEX($A$2:$A$8,ROW($A$2:$A$8)-ROW($A$2)+1,1)=$A$2)*(ROW($A$2:$A$8)-ROW($A$2)+1))),1),"")<=J$1)*(IFERROR(INDEX($I$2:$I$8,1/(1/((INDEX($A$2:$A$8,ROW($A$2:$A$8)-ROW($A$2)+1,1)=$A$2)*(ROW($A$2:$A$8)-ROW($A$2)+1))),1),"")>=J$1))>0,FALSE)
K2:AF8	K2	=IF($A2<>$A1,SUM((IFERROR(INDEX($H$2:$H$8,1/(1/((INDEX($A$2:$A$8,ROW($A$2:$A$8)-ROW($A$2)+1,1)=$A2)*(ROW($A$2:$A$8)-ROW($A$2)+1))),1),"")<=K$1)*(IFERROR(INDEX($I$2:$I$8,1/(1/((INDEX($A$2:$A$8,ROW($A$2:$A$8)-ROW($A$2)+1,1)=$A2)*(ROW($A$2:$A$8)-ROW($A$2)+1))),1),"")>=K$1))>0,FALSE)
N12:AF12	N12	=SUM(($K$12<=K$11)*($L$12>=K$1))>0
J12	J12	=IF(A12<>A11,,)
K12:K18	K12	=IFERROR(INDEX($H$12:$H$18,1/(1/((INDEX($A$12:$A$18,ROW($A$12:$A$18)-ROW($A$12)+1,1)=$A$12)*(ROW($A$12:$A$18)-ROW($A$12)+1))),1),"")
L12:L18	L12	=IFERROR(INDEX($I$12:$I$18,1/(1/((INDEX($A$12:$A$18,ROW($A$12:$A$18)-ROW($A$12)+1,1)=$A$12)*(ROW($A$12:$A$18)-ROW($A$12)+1))),1),"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:AF8	Expression	=NOT(J2)	text	NO
J2:AF8	Expression	=J2	text	NO

 

[haziqFARHAN]

Hi Sir, thank you very much. For current formula I insert in Conditional Formatting as below image and not in each cell.

Is it possible to make as I wanted by only insert he formula in this Conditional Formatting ?

 

[JamesCanale]

I usually just put the whole formula into the conditional formatting only. I didn't like the putting values into the cells and then using that to format. But when I put the formula straight into conditional formatting, it wasn't working right. I didn't have time to get to the bottom of it.