PDA

View Full Version : [SOLVED] Problem with date comparisson



H3avyMetal
10-23-2018, 11:05 AM
Hello,

I am working on a calendar generator in Excel using VBA, i have chosen this route cause a special formating is needed for the calendar and also because everything i can automate is a good thing.

I got the generator running as intended, but now i want to mark holidays like christmas or easter. This part gives me a hard time.

So heres some (shortened and simplified) example code



Sub holiday()
Dim holidays(1 to 2) as Date
holidays(1) = 01.01.2018
holidays(2) = 06.01.2018
Dim day as Byte
Dim month as Byte
Dim counter as integer
Dim holidayDay as integer
Dim holidayMonth as Integer

For month = 1 to 1
For day = 1 to 31
Cells(1,day).value = day
For counter = 1 to 2
holidayDay = Format(holidays(counter), "DD")
holidayMonth = Format(holidays(counter), "MM")

If holidayDay = day and holidayMonth = month Then
Cells(1,day).interior.colorindex = 3
End If
Next counter
Next day
Next month
End Sub

So basically i want the cell with the corresponding day to be colored. What happend was that i get the coloring of by a 1 (so instead of the 6th gets colored the 5th gets colored and the first one does not get colored at all.

Oh and the first for loop should go from 1 to 12 cause a new worksheet gets generated for each month.

I hope you can help.

If it is needed i can provide the whole code tomorrow when i have access to my laptop.

Best Regards
Christian

p45cal
10-23-2018, 11:50 AM
Sub holiday()
Dim holidays(1 To 2) ' As Date
Dim day As Byte
Dim month As Byte
Dim myDate ' As Date

holidays(1) = CLng(CDate("2018/1/1"))
holidays(2) = CLng(CDate("2018/1/6"))

For month = 1 To 1
For day = 1 To 31
Cells(1, day).Value = day
If Not IsError(Application.Match(CLng(DateSerial(2018, month, day)), holidays, 0)) Then Cells(1, day).Interior.ColorIndex = 3
Next day
Next month
End Sub
?

H3avyMetal
10-23-2018, 09:36 PM
Thanks p45cal

I had to tweak the code a little bit but now it works as intended