conditional formatting and MATCH variable

[grunschlange]

Good morning. I have a calendar that will show various due dates. Each month takes up 4 columns, with the actual day of the month in the first (i.e. January's actual dates are in A3:A33, February's in F3:F31, March's in K3:K33, April's in P3:P32, etc.). I currently have the serial numbers for dates listed, but I will change the format later to have dates as m/dd format. My goal is to have a calendar that will change and be usable each year depending on the input of the first day and year.

I have the following code to go down each date column (i.e. A, F, K, P, etc.) and change the color to red if the date is a holiday listed in a "Holidays" named range, yellow if it is a general office closure listed in a "Closures" named range, and green if it is a Saturday or Sunday.


Sub test10()
'
' test10 Macro
'
'
Dim myEndRow As Integer
Dim myMonth1 As Long
Dim myMonth2 As Integer
Dim myStartDate As Date


Range("A3").Select
myStartDate = ActiveCell.Value
myMonth = Month(myStartDate)
If myMonth = 4 Then
myEndRow = 30
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
ElseIf myMonth = 6 Then
myEndRow = 30
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
ElseIf myMonth = 9 Then
myEndRow = 30
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
ElseIf myMonth = 11 Then
myEndRow = 30
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
ElseIf myMonth = 2 Then
If Range("K3").Value = Range("F31").Value Then
myEndRow = 28
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
Else
myEndRow = 29
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
End If
Else
myEndRow = 31
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
End If


With Selection.FormatConditions.Add(xlExpression, Formula1:="=MATCH(myStartDate,Holidays,0)>0")
With .Interior
.ColorIndex = 3
End With
End With
With Selection.FormatConditions.Add(xlExpression, Formula1:="=MATCH(myStartDate,Furloughs,0)>0")
With .Interior
.ColorIndex = 6
End With
End With
With Selection.FormatConditions.Add(xlExpression, Formula1:="=OR(WEEKDAY(myStartDate)=1,WEEKDAY(myStartDate)=7)")
With .Interior
.ColorIndex = 4
End With
End With
Selection.Offset(0, 5).Range("A1").Select
If ActiveCell.Value = "" Then
Application.Goto Reference:="R1C1"
Exit Sub
Else
ActiveCell.Select
End If


Do
myStartDate = ActiveCell.Value
myMonth = Month(myStartDate)
If myMonth = 4 Then
myEndRow = 30
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
ElseIf myMonth = 6 Then
myEndRow = 30
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
ElseIf myMonth = 9 Then
myEndRow = 30
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
ElseIf myMonth = 11 Then
myEndRow = 30
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
ElseIf myMonth = 2 Then
If Range("K3").Value = Range("F31").Value Then
myEndRow = 28
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
Else
myEndRow = 29
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
End If
Else
myEndRow = 31
Selection.Offset(0, 0).Range("A1:A" & myEndRow & "").Select
End If


With Selection.FormatConditions.Add(xlExpression, Formula1:="=MATCH(myStartDate,Holidays,0)>0")
With .Interior
.ColorIndex = 3
End With
End With
With Selection.FormatConditions.Add(xlExpression, Formula1:="=MATCH(myStartDate,Furloughs,0)>0")
With .Interior
.ColorIndex = 6
End With
End With
With Selection.FormatConditions.Add(xlExpression, Formula1:="=OR(WEEKDAY(myStartDate)=1,WEEKDAY(myStartDate)=7)")
With .Interior
.ColorIndex = 4
End With
End With
Selection.Offset(0, 5).Range("A1").Select
If ActiveCell.Value = "" Then
Application.Goto Reference:="R1C1"
Exit Sub
Else
ActiveCell.Select
End If
Loop
End Sub



Instead of copying the code 12 times in the module for each month, I am trying to loop the code, so that it would read the active cell (i.e. A3 or F3 or K3 or P3, etc...depending on the month read) as a variable. I'm not having any luck using a variable in the conditional formatting formulas. If I substitute "A3" for the formatting variable, it works fine for January's column, but all of the other months are also reading "A3" when I tried to loop.

Can you let me know what I am doing wrong and what I can do to fix this?
Thanks.