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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube