» Enter the holiday according to the Table in the holiday-list using VBA

Question:
I have the EmployeeName, the HolidayStart and the HolidayEnd in a Worksheet. How can I color the holidays of each employee in the following month sheets?

Answer:
Enter the following Code with XL5/7 in an ModuleSheet, with XL8 in a general Module, assign it to a Button and run it.






Place the code below into the standard module

Sub NewVacation()

   Dim rngFind As Range

   Dim intRow As Integer, intMonth As Integer, intCounter As Integer

   intRow = 3

   Do Until IsEmpty(Cells(intRow, 1))

      For intMonth = Month(Cells(intRow, 2)) To Month(Cells(intRow, 3))

         Set rngFind = Worksheets(Format(DateSerial(1, intMonth, 1), "mmmm")). _

            Columns(1).Find _

            (Cells(intRow, 1), LookIn:=xlValues, lookat:=xlWhole)

         If intMonth = Month(Cells(intRow, 2)) And intMonth = _

            Month(Cells(intRow, 3)) Then

            For intCounter = Day(Cells(intRow, 2)) To Day(Cells(intRow, 3))

               rngFind.Offset(0, intCounter).Interior.ColorIndex = 3

            Next intCounter

         ElseIf intMonth = Month(Cells(intRow, 2)) Then

            For intCounter = Day(Cells(intRow, 2)) To Day(DateSerial _

               (1, Month(Cells(intRow, 2)) + 1, 0))

               rngFind.Offset(0, intCounter).Interior.ColorIndex = 3

            Next intCounter

         Else

            For intCounter = 1 To Day(Cells(intRow, 3))

               rngFind.Offset(0, intCounter).Interior.ColorIndex = 3

            Next intCounter

         End If

      Next intMonth

      intRow = intRow + 1

   Loop

End Sub