Conditional & dynamically filling cell or range with Excel VBA

[Cakz Primz]

Dear all,

I have two columns of "annual leave" date, as below:
column AB = Start Date
column AC = End Date

While in column AJ, cell AJ1 until cell PO1 I have list of sequence date, starting from Dec 1, 2024 until Dec 31, 2025.

Let's say in cell:
AB2 = Dec 22, 2024
AC2 = Dec 27, 2025
Duration = 5 days

How to write "L" in row # 2, starting from column with the date equal to AB2 (Start Date) until column with the date equal to AC2 (End Date), using Excel VBA or Excel formula?

Thank you very much in advance.

Best regards,
prima - Indonesia

 

[kevin9999]

Something like this perhaps? (I've hidden a lot of columns to demonstrate)

Book1
	AB	AC	AD																									BC	BD	BE	BF	BG	BH	BI	BJ	BK																																																																																																																																																																																																																																																																																																																																																																								PG	PH	PI	PJ	PK	PL	PM	PN	PO
1	Start Date	End Date																										Dec 20, 2024	Dec 21, 2024	Dec 22, 2024	Dec 23, 2024	Dec 24, 2024	Dec 25, 2024	Dec 26, 2024	Dec 27, 2024	Dec 28, 2024																																																																																																																																																																																																																																																																																																																																																																								Dec 23, 2025	Dec 24, 2025	Dec 25, 2025	Dec 26, 2025	Dec 27, 2025	Dec 28, 2025	Dec 29, 2025	Dec 30, 2025	Dec 31, 2025
2	Dec 22, 2024	Dec 27, 2025																												L	L	L	L	L	L	L																																																																																																																																																																																																																																																																																																																																																																								L	L	L	L	L
3
Sheet1

Cell Formulas
Range	Formula
BC2:BK2,PG2:PO2	BC2	=IF(AND(BC1>=$AB$2,BC1<=$AC$2),"L","")

 

[kvsrinivasamurthy]

Try. In AJ2. column AA and AB are taken from row 2 to 4. Adjust as required.

=LET(dts,AJ1:PO1,MAKEARRAY(ROWS(AA2:AA4),COLUMNS(dts),LAMBDA(r,c,IF((INDEX(AA2:AA4,r)<=INDEX(dts,c))*(INDEX(AB2:AB4,r)>=INDEX(dts,c)),"L",""))))

 

[Cakz Primz]

Dear kevin9999,

Thank you so much, the formula given is working very well.
May God bless you.

Problem solved.

Best regards,
prima - Indonesia

 

[kevin9999]

Happy to help Prima, and thanks for the feedback