Conditional format if date values from two cells are within Qtr/Year Range

[lewjan]

Hello,
Thanks in advance for your help! I am looking for macro/vba to help highlight a range of cells within the quarter/year columns if date value falls within baseline start and baseline end?

	baseline start	baseline end	2021 Q1	2021 Q2	2021 Q3	2021 Q4	2022 Q1	2022 Q2	2022 Q3	2022 Q4
apple	25-Feb-21​	30-Dec-21​	fill in green color	fill in green color	fill in green color	fill in green color
orange	30-Apr-21​	30-May-22​		fill in green color	fill in green color	fill in green color	fill in green color	fill in green color

Grateful for your help!
Jan

 

[JamesCanale]

No VBA, and added a helper row.

MrExcelPlayground.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1				1/1/2021	4/1/2021	7/1/2021	10/1/2021	1/1/2022	4/1/2022	7/1/2022	10/1/2022
2		baseline start	baseline end	2021 Q1	2021 Q2	2021 Q3	2021 Q4	2022 Q1	2022 Q2	2022 Q3	2022 Q4
3	apple	2/25/2021	12/31/2021
4	orange	4/30/2021	5/30/2022
Sheet49

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D3:K4	Expression	=OR(ROW(INDIRECT(YEAR($B3)*100+MONTH($B3)&":"&YEAR($C3)*100+MONTH($C3)))=YEAR(D$1)*100+MONTH(D$1),ROW(INDIRECT(YEAR($B3)*100+MONTH($B3)&":"&YEAR($C3)*100+MONTH($C3)))=YEAR(D$1+45)*100+MONTH(D$1+45),ROW(INDIRECT(YEAR($B3)*100+MONTH($B3)&":"&YEAR($C3)*100+MONTH($C3)))=YEAR(D$1+70)*100+MONTH(D$1+70))	text	NO

 

[Fluff]

Another option without helper cells

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		baseline start	baseline end	2021 Q1	2021 Q2	2021 Q3	2021 Q4	2022 Q1	2022 Q2	2022 Q3	2022 Q4
2	apple	25/02/2021	30/12/2021
3	orange	30/04/2021	30/05/2022
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:K3	Expression	=AND($B2<=DATE(LEFT(D$1,4),RIGHT(D$1)*3,1),$C2>=DATE(LEFT(D$1,4),RIGHT(D$1)*3-2,1))	text	NO

 

[lewjan]

THANK YOU FLUFF and JamesCanale!! You saved me hours of banging my head on a wall!!

 

[Fluff]

Glad we could help & thanks for the feedback.