Conditional Formatting Based on Quarterly Data that is Text Not Related to Actual Dates

[muleevp]

Hello, I am trying to use conditional formatting to make a Gantt chart. I need to color the number of quarters in each row based on the StartQtr and NbrQtrs. I know how to do one column based on the starting quarter, but I don't know how to do multiple columns based on the number of quarters. Any suggestions?

StartQtr

EndQtr

QtrDiff

YrDiff

NbrQtrs

1Q23

2Q23

3Q23

4Q23

1Q24

2Q24

3Q24

4Q24

1Q23

1Q23

0​

0​

1​

2Q23

4Q23

2​

0​

3​

1Q24

4Q24

3​

0​

4​

1Q23

4Q24

3​

1​

8​

3Q23

3Q24

0​

1​

5​

4Q24

2Q25

-2​

1​

3​

4Q24

1Q25

-3​

1​

2​

4Q23

3Q26

-1​

3​

12​

3Q23

2Q27

-1​

4​

16​

4Q23

2Q26

-2​

3​

11​

4Q23

1Q25

-3​

2​

6​

4Q23

4Q26

0​

3​

13​

4Q23

3Q24

-1​

1​

4​

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	StartQtr	EndQtr	QtrDiff	YrDiff	NbrQtrs	1Q23	2Q23	3Q23	4Q23	1Q24	2Q24	3Q24	4Q24
2	1Q23	1Q23	0	0	1
3	2Q23	4Q23	2	0	3
4	1Q24	4Q24	3	0	4
5	1Q23	4Q24	3	1	8
6	3Q23	3Q24	0	1	5
7	4Q24	2Q25	-2	1	3
8	4Q24	1Q25	-3	1	2
9	4Q23	3Q26	-1	3	12
10	3Q23	2Q27	-1	4	16
11	4Q23	2Q26	-2	3	11
12	4Q23	1Q25	-3	2	6
13	4Q23	4Q26	0	3	13
14	4Q23	3Q24	-1	1	4
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:M14	Expression	=AND(COLUMNS($F1:F1)>=MATCH($A2,$F$1:$M$1,0),COLUMNS($F1:F1)<=MATCH($A2,$F$1:$M$1,0)+$E2-1)	text	NO

 

[muleevp]

This is perfect - thanks so much for your help!!

 

[Fluff]

You're welcome & thanks for the feedback.