Conditional Formatting Based on Multiple Dates

[muleevp]

Hello - I am trying to use conditional formatting to highlight individual dates and make a Gantt Chart to look like the following:

The data I am using comes from this chart:

I know I need three conditional formatting commands to get the different colors, but I only seem to be able to get it to highlight the first date in each row. Thanks for any help you can provide!

 

[Toadstool]

Hi Muleevp,
I'm a little confused as to why 31 July Group 6 is highlighted for M1 as I don't see July 31, 2021 in the MODE1 row?

 

[Toadstool]

Hi Muleevp,

If I assume for my previous question that it was an error then this should satisfy your requirement.
NOTE: If the same date and group exists for more than one Mode then only the first is shown.

Muleevp.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	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1	Grp	12-Jul	13-Jul	14-Jul	15-Jul	16-Jul	17-Jul	18-Jul	19-Jul	20-Jul	21-Jul	22-Jul	23-Jul	24-Jul	25-Jul	26-Jul	27-Jul	28-Jul	29-Jul	30-Jul	31-Jul	1-Aug	2-Aug	3-Aug	4-Aug	5-Aug	6-Aug	7-Aug	8-Aug	9-Aug	10-Aug	11-Aug	12-Aug	13-Aug	14-Aug	15-Aug	16-Aug	17-Aug	18-Aug	19-Aug	20-Aug	21-Aug	22-Aug
2	0	M1	M2												M3
3	1				M1			M2										M3
4	2					M1			M2															M3
5	3										M1		M2														M3
6	4													M1		M2														M3
7	5																	M1	M2													M3
8	6																								M1	M2									M3
9
10
11		0	1	2	3	4	5	6
12	MODE1	12-Jul-21	15-Jul-21	16-Jul-21	21-Jul-21	24-Jul-21	28-Jul-21	04-Aug-21
13	MODE2	13-Jul-21	18-Jul-21	19-Jul-21	23-Jul-21	26-Jul-21	29-Jul-21	05-Aug-21
14	MODE3	25-Jul-21	28-Jul-21	03-Aug-21	06-Aug-21	09-Aug-21	11-Aug-21	14-Aug-21
AGG

Cell Formulas
Range	Formula
C1:AQ1	C1	=B1+1
B2:AQ8	B2	=IFERROR("M"&RIGHT(INDEX($A$12:$A$14,AGGREGATE(15,6,ROW($B$12:$H$14)-ROW($A$11)/(($B$12:$H$14=B$1)*($B$11:$H$11=$A2)),1)),1),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:AQ8	Cell Value	="M3"	text	NO
B2:AQ8	Cell Value	="M2"	text	NO
B2:AQ8	Cell Value	="M1"	text	NO