Color cells if workday and holiday (VBA)

[Retroshift]

Hello, I got a macro button which adds month sheets for a specific year that is entered in an inputbox.
The following code unsuccessfully attempts to fill all working days with a pink colour if they match a range of cells that represent the holidays (and include blank cells).
Is there a way to make the code work?

            For Each rng In Range("B1").Resize(2, Day(DateSerial(yr, i + 1, 1) - 1))
                datearray = Application.Transpose(Worksheets("Holidays").Range("C8:C42")) 'includes blank cells
                If Weekday(rng) > 1 And Weekday(rng) < 7 And Filter(datearray, CLng(rng), , vbTextCompare) Then
                rng.Interior.Color = RGB(255, 204, 255)
                End If
            Next

 

[jdellasala]

VBA is NOT the Holy Grail! All you need is Conditional Formatting. The easiest way to set it up is to create a formula in the worksheet that results in True/False with respect to the top left corner of the conditional area. It's not important here since you're only looking at one column. Here's how to do it:

Excel VBA User Defined Function To Generate A Dynamic Array - 2516.xlsm
	A	B	C
1	Date	CF	Holidays
2	01/01/2022	FALSE	01/01/2021
3	01/02/2022	FALSE	01/18/2021
4	01/03/2022	FALSE	02/15/2021
5	01/04/2022	FALSE	04/02/2021
6	01/05/2022	FALSE	05/31/2021
7	01/06/2022	FALSE	07/05/2021
8	01/07/2022	FALSE	09/06/2021
9	01/08/2022	FALSE	11/25/2021
10	01/09/2022	FALSE	12/24/2021
11	01/10/2022	FALSE	12/31/2021
12	01/11/2022	FALSE	01/17/2022
13	01/12/2022	FALSE	02/21/2022
14	01/13/2022	FALSE	04/15/2022
15	01/14/2022	FALSE	05/30/2022
16	01/15/2022	FALSE	07/04/2022
17	01/16/2022	FALSE	09/05/2022
18	01/17/2022	TRUE	11/24/2022
19	01/18/2022	FALSE	12/26/2022
20	01/19/2022	FALSE
21	01/20/2022	FALSE
22	01/21/2022	FALSE
23	01/22/2022	FALSE
24	01/23/2022	FALSE
25	01/24/2022	FALSE
26	01/25/2022	FALSE
27	01/26/2022	FALSE
28	01/27/2022	FALSE
29	01/28/2022	FALSE
30	01/29/2022	FALSE
31	01/30/2022	FALSE
32	01/31/2022	FALSE
Sheet4

Cell Formulas
Range	Formula
B2:B32	B2	=ISNUMBER(MATCH($A2,$C$2:$C$19,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A32	Expression	=ISNUMBER(MATCH($A2,$C$2:$C$19,0))	text	NO