Conditional Formatting Of One Array Into Another

[rohitnigade]

Hi,

I have an array which is a dynamic calendar (B4:H9) (Let's call it 'Calendar Array"). I have another array(K4:Q9), which is also dynamic, but it only contains the dates that an employee is on leave (Let's call it 'Leave Array"). The Calendar Array is populated based on the inputs in C1 and E1, the Leave array is populated based on the inputs in L2:M2. I want to highlight these leave dates in the Calendar array. I could do this using duplicate values, but the Leave Array here is only for representation, in reality it would not be here and should only be used for the Conditional Formatting formula. I need help with creating such formula for Conditional Formatting that will highlight the dates in Calendar Array based on the inputs in L2:M2.

Any help will be really appreciated.

 

[rohitnigade]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Year	2022	Month	10							From	To
2											Absent Dates	08-10-2022	15-10-2022
3		MON	TUE	WED	THU	FRI	SAT	SUN
4		26	27	28	29	30	01	02			08	09	10	11	12	13	14
5		03	04	05	06	07	08	09			15
6		10	11	12	13	14	15	16
7		17	18	19	20	21	22	23
8		24	25	26	27	28	29	30
9		31	01	02	03	04	05	06
10
11
12
Sheet1

Cell Formulas
Range	Formula
B4:H9	B4	=SEQUENCE(6,7,DATE(C1,E1,1)-WEEKDAY(DATE(C1,E1,1),2)+1,1)
K4:Q9	K4	=IF(SEQUENCE(6,7,L2,1)>M2,"",SEQUENCE(6,7,L2,1))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:H9	Expression	=MONTH(B4)=$E$1	text	NO
B4:H9	Expression	=MONTH(B4)<>$E$1	text	NO

 

[Fluff]

How about using

=AND(B4>=$L$2,B4<=$M$2)

 

[rohitnigade]

How about using

=AND(B4>=$L$2,B4<=$M$2)

Man you are a lifesaver! Thank you so much. I couldn't find even when it was this simple.

 

[Fluff]

You're welcome & thanks for the feedback.