Toonies
09-05-2011, 11:31 AM
Hi I am looking for help and advice on how to do the following with the vba code that I have found
I have a Named Range of Public Holiday dates that I want the VBA to ignore when it is highlighting vacation planner
I am using this coding that excludes weekends, which works
can it be altered to exclude the named range of Public holiday dates.
the named range is called "PUBLICHOILDAY"
here is the coding
Private Sub CommandButton1_Click()
Dim Rng As Range, Dn As Range
Dim sDt As Date
Dim eDt As Date
Dim Ac As Integer
Dim col As Integer
Set Rng = Range(Range("B5"), Range("B" & Rows.Count).End(xlUp))
sDt = ComboBox1
eDt = ComboBox2
Select Case True
Case Is = holidayButton1: col = 43
Case Is = sickLeaveButton3: col = 53
Case Is = otherOptionButton4: col = 37
End Select
For Each Dn In Rng
If Dn = nameBox1 Then
For Ac = 1 To 366 ' Change to 366
If Weekday(Cells(4, Ac + 2), vbMonday) < 6 Then
If Cells(4, Ac + 2) >= sDt And Cells(4, Ac + 2) <= eDt Then
Dn.Offset(, Ac).Interior.ColorIndex = col
End If
End If
Next Ac
End If
Next Dn
Unload Me
End Sub
the original coding can be found at
http://www.mrexcel.com/forum/showthread.php?t=539877&highlight=public+holiday
Many thanks
Toonies
I have a Named Range of Public Holiday dates that I want the VBA to ignore when it is highlighting vacation planner
I am using this coding that excludes weekends, which works
can it be altered to exclude the named range of Public holiday dates.
the named range is called "PUBLICHOILDAY"
here is the coding
Private Sub CommandButton1_Click()
Dim Rng As Range, Dn As Range
Dim sDt As Date
Dim eDt As Date
Dim Ac As Integer
Dim col As Integer
Set Rng = Range(Range("B5"), Range("B" & Rows.Count).End(xlUp))
sDt = ComboBox1
eDt = ComboBox2
Select Case True
Case Is = holidayButton1: col = 43
Case Is = sickLeaveButton3: col = 53
Case Is = otherOptionButton4: col = 37
End Select
For Each Dn In Rng
If Dn = nameBox1 Then
For Ac = 1 To 366 ' Change to 366
If Weekday(Cells(4, Ac + 2), vbMonday) < 6 Then
If Cells(4, Ac + 2) >= sDt And Cells(4, Ac + 2) <= eDt Then
Dn.Offset(, Ac).Interior.ColorIndex = col
End If
End If
Next Ac
End If
Next Dn
Unload Me
End Sub
the original coding can be found at
http://www.mrexcel.com/forum/showthread.php?t=539877&highlight=public+holiday
Many thanks
Toonies