Solved: VBA to exclude Public Holidays

[Toonies]

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

[Bob Phillips]

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
If IsError(Application.Match(Cells(4, Ac + 2).Range("PUBLICHOLIDAY"),0)) Then
If Dn.Offset(, Ac).Interior.ColorIndex = col
End If
End If
End If
Next Ac
End If
Next Dn
Unload Me
End Sub

Not sure what you are doing in that code, but the loop smells of being inefficient.

[Toonies]

Hi xld,

Thanks for your quick reply

I have tried it and get the following error message

Compile error
Syntax error

with the following part of the code in red

If Dn.Offset(, Ac).Interior.ColorIndex = col

and this part highlighted in yellow

Private Sub CommandButton1_Click()

I have attached a copy of the file

many thanks

[Paul_Hossler]

Try just this


Dn.Offset(, Ac).Interior.ColorIndex = col


Paul

[Toonies]

Hi I tried your suggestion and got the following error message

Run-time error '1004':
Application-defined or object-defined error

when I try to Debug, it then shows the following part of the code highlighted in yellow

If IsError(Application.Match(Cells(4, Ac + 2).Range("PUBLICHOLIDAY"), 0)) Then

many thanks

[Paul_Hossler]

I think there should be a comma after the + 2) and not a period


If IsError(Application.Match(Cells(4, Ac + 2),Range("PUBLICHOLIDAY"),0))

Paul

[Bob Phillips]

Sorry =col should have been = vbRed as well as the comma thing Paul pointed out.

[Toonies]

Many thanks to both of you it works a treat.
:beerchug:

I'm only just getting into VBA is there any good pointers or books that you could recommend.

Toonies

[Bob Phillips]

My only advice mate is to follow lots of threads, see what others ask and see if you can follow the answers. As time goes on, try to answer some. It is all about experience and trying yourself. Even better if you have some good projects to sink your teeth into.

Books on VBA don't really do it for me.

[Toonies]

Many thanks for your time and advice

Toonies