Can someone tell me what's wrong with my code? For loop

P

Pritch558

New Member
Joined
Jan 30, 2015
Messages
18
Hi, can someone have a look at the code below. It's long, but repetitive. Each IF statement works but only for the first IF comparable, and the ELSE. All of the ElseIF's don't seem to be working and I can't figure out why.
Trying to spruce up the holiday tracker for my workforce.
Column "AS" starting from Row 39 is a list of ID numbers. Row 38 is dates. When ran, the macro populates a selection of labels on a Userform as a month view calendar. If the employee with that ID number has no holiday (or other as shown in vba code below) it displays the date in the label (as "dd"). This works fine. If however the employee with that ID number does have a holiday, it's supposed to populate the label with the content of the associated cell on their row (Referred as "i"). Which for example would be the letter "H" for Holiday. There's some colour changes and things too.
Once I have April working I'll repeat for the other months.

Your help would be greatly appreciated. Thanks.

VBA Code: 
Sub FormApril()
' ----------------------------------------- April -----------------------------------------------------
  Dim totRows As Long, i As Long

  totRows = Worksheets("Report Calc").Range("AS38").CurrentRegion.Rows.Count
  For i = 39 To totRows
  
  
        If Worksheets("Report Calc").Cells(i, 54) = "0" Then                 '0 - Date
                    UserForm1.lbl0401.Caption = Worksheets("Report Calc").Cells(38, 54) '54
                    UserForm1.lbl0401.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 54) = "H" Then         'H - Holiday
                    UserForm1.lbl0401.Caption = Worksheets("Report Calc").Cells(i, 54)
                    UserForm1.lbl0401.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 54) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0401.Caption = Worksheets("Report Calc").Cells(i, 54)
                    UserForm1.lbl0401.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 54) = "L" Then         'L - Leave
                    UserForm1.lbl0401.Caption = Worksheets("Report Calc").Cells(i, 54)
                    UserForm1.lbl0401.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 54) = "A" Then         'A - Absence
                    UserForm1.lbl0401.Caption = Worksheets("Report Calc").Cells(i, 54)
                    UserForm1.lbl0401.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 54) = "R" Then          'R - Rest Day
                    UserForm1.lbl0401.Caption = Worksheets("Report Calc").Cells(i, 54)
                    UserForm1.lbl0401.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0401.Caption = ""
        End If
        
        If Worksheets("Report Calc").Cells(i, 55) = "0" Then                 '0 - Date
                    UserForm1.lbl0402.Caption = Worksheets("Report Calc").Cells(38, 55) '55
                    UserForm1.lbl0402.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 55) = "H" Then         'H - Holiday
                    UserForm1.lbl0402.Caption = Worksheets("Report Calc").Cells(i, 55)
                    UserForm1.lbl0402.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 55) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0402.Caption = Worksheets("Report Calc").Cells(i, 55)
                    UserForm1.lbl0402.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 55) = "L" Then         'L - Leave
                    UserForm1.lbl0402.Caption = Worksheets("Report Calc").Cells(i, 55)
                    UserForm1.lbl0402.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 55) = "A" Then         'A - Absence
                    UserForm1.lbl0402.Caption = Worksheets("Report Calc").Cells(i, 55)
                    UserForm1.lbl0402.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 55) = "R" Then          'R - Rest Day
                    UserForm1.lbl0402.Caption = Worksheets("Report Calc").Cells(i, 55)
                    UserForm1.lbl0402.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0402.Caption = ""
        End If

        If Worksheets("Report Calc").Cells(i, 56) = "0" Then                 '0 - Date
                    UserForm1.lbl0403.Caption = Worksheets("Report Calc").Cells(38, 56) '56
                    UserForm1.lbl0403.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 56) = "H" Then         'H - Holiday
                    UserForm1.lbl0403.Caption = Worksheets("Report Calc").Cells(i, 56)
                    UserForm1.lbl0403.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 56) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0403.Caption = Worksheets("Report Calc").Cells(i, 56)
                    UserForm1.lbl0403.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 56) = "L" Then         'L - Leave
                    UserForm1.lbl0403.Caption = Worksheets("Report Calc").Cells(i, 56)
                    UserForm1.lbl0403.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 56) = "A" Then         'A - Absence
                    UserForm1.lbl0403.Caption = Worksheets("Report Calc").Cells(i, 56)
                    UserForm1.lbl0403.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 56) = "R" Then          'R - Rest Day
                    UserForm1.lbl0403.Caption = Worksheets("Report Calc").Cells(i, 56)
                    UserForm1.lbl0403.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0403.Caption = ""
        End If

        If Worksheets("Report Calc").Cells(i, 57) = "0" Then                 '0 - Date
                    UserForm1.lbl0404.Caption = Worksheets("Report Calc").Cells(38, 57)
                    UserForm1.lbl0404.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 57) = "H" Then         'H - Holiday
                    UserForm1.lbl0404.Caption = Worksheets("Report Calc").Cells(i, 57)
                    UserForm1.lbl0404.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 57) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0404.Caption = Worksheets("Report Calc").Cells(i, 57)
                    UserForm1.lbl0404.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 57) = "L" Then         'L - Leave
                    UserForm1.lbl0404.Caption = Worksheets("Report Calc").Cells(i, 57)
                    UserForm1.lbl0404.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 57) = "A" Then         'A - Absence
                    UserForm1.lbl0404.Caption = Worksheets("Report Calc").Cells(i, 57)
                    UserForm1.lbl0404.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 57) = "R" Then          'R - Rest Day
                    UserForm1.lbl0404.Caption = Worksheets("Report Calc").Cells(i, 57)
                    UserForm1.lbl0404.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0404.Caption = ""
        End If

        If Worksheets("Report Calc").Cells(i, 58) = "0" Then                 '0 - Date
                    UserForm1.lbl0405.Caption = Worksheets("Report Calc").Cells(38, 58)
                    UserForm1.lbl0405.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 58) = "H" Then         'H - Holiday
                    UserForm1.lbl0405.Caption = Worksheets("Report Calc").Cells(i, 58)
                    UserForm1.lbl0405.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 58) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0405.Caption = Worksheets("Report Calc").Cells(i, 58)
                    UserForm1.lbl0405.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 58) = "L" Then         'L - Leave
                    UserForm1.lbl0405.Caption = Worksheets("Report Calc").Cells(i, 58)
                    UserForm1.lbl0405.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 58) = "A" Then         'A - Absence
                    UserForm1.lbl0405.Caption = Worksheets("Report Calc").Cells(i, 58)
                    UserForm1.lbl0405.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 58) = "R" Then          'R - Rest Day
                    UserForm1.lbl0405.Caption = Worksheets("Report Calc").Cells(i, 58)
                    UserForm1.lbl0405.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0405.Caption = ""
        End If

        If Worksheets("Report Calc").Cells(i, 59) = "0" Then                 '0 - Date
                    UserForm1.lbl0406.Caption = Worksheets("Report Calc").Cells(38, 59)
                    UserForm1.lbl0406.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 59) = "H" Then         'H - Holiday
                    UserForm1.lbl0406.Caption = Worksheets("Report Calc").Cells(i, 59)
                    UserForm1.lbl0406.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 59) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0406.Caption = Worksheets("Report Calc").Cells(i, 59)
                    UserForm1.lbl0406.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 59) = "L" Then         'L - Leave
                    UserForm1.lbl0406.Caption = Worksheets("Report Calc").Cells(i, 59)
                    UserForm1.lbl0406.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 59) = "A" Then         'A - Absence
                    UserForm1.lbl0406.Caption = Worksheets("Report Calc").Cells(i, 59)
                    UserForm1.lbl0406.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 59) = "R" Then          'R - Rest Day
                    UserForm1.lbl0406.Caption = Worksheets("Report Calc").Cells(i, 59)
                    UserForm1.lbl0406.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0406.Caption = ""
        End If

        If Worksheets("Report Calc").Cells(i, 60) = "0" Then                 '0 - Date
                    UserForm1.lbl0407.Caption = Worksheets("Report Calc").Cells(38, 60)
                    UserForm1.lbl0407.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 60) = "H" Then         'H - Holiday
                    UserForm1.lbl0407.Caption = Worksheets("Report Calc").Cells(i, 60)
                    UserForm1.lbl0407.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 60) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0407.Caption = Worksheets("Report Calc").Cells(i, 60)
                    UserForm1.lbl0407.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 60) = "L" Then         'L - Leave
                    UserForm1.lbl0407.Caption = Worksheets("Report Calc").Cells(i, 60)
                    UserForm1.lbl0407.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 60) = "A" Then         'A - Absence
                    UserForm1.lbl0407.Caption = Worksheets("Report Calc").Cells(i, 60)
                    UserForm1.lbl0407.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 60) = "R" Then          'R - Rest Day
                    UserForm1.lbl0407.Caption = Worksheets("Report Calc").Cells(i, 60)
                    UserForm1.lbl0407.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0407.Caption = ""
        End If

        If Worksheets("Report Calc").Cells(i, 61) = "0" Then                 '0 - Date
                    UserForm1.lbl0408.Caption = Worksheets("Report Calc").Cells(38, 61)
                    UserForm1.lbl0408.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 61) = "H" Then         'H - Holiday
                    UserForm1.lbl0408.Caption = Worksheets("Report Calc").Cells(i, 61)
                    UserForm1.lbl0408.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 61) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0408.Caption = Worksheets("Report Calc").Cells(i, 61)
                    UserForm1.lbl0408.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 61) = "L" Then         'L - Leave
                    UserForm1.lbl0408.Caption = Worksheets("Report Calc").Cells(i, 61)
                    UserForm1.lbl0408.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 61) = "A" Then         'A - Absence
                    UserForm1.lbl0408.Caption = Worksheets("Report Calc").Cells(i, 61)
                    UserForm1.lbl0408.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 61) = "R" Then          'R - Rest Day
                    UserForm1.lbl0408.Caption = Worksheets("Report Calc").Cells(i, 61)
                    UserForm1.lbl0408.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0408.Caption = ""
        End If

        If Worksheets("Report Calc").Cells(i, 62) = "0" Then                 '0 - Date
                    UserForm1.lbl0409.Caption = Worksheets("Report Calc").Cells(38, 62)
                    UserForm1.lbl0409.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 62) = "H" Then         'H - Holiday
                    UserForm1.lbl0409.Caption = Worksheets("Report Calc").Cells(i, 62)
                    UserForm1.lbl0409.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 62) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0409.Caption = Worksheets("Report Calc").Cells(i, 62)
                    UserForm1.lbl0409.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 62) = "L" Then         'L - Leave
                    UserForm1.lbl0409.Caption = Worksheets("Report Calc").Cells(i, 62)
                    UserForm1.lbl0409.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 62) = "A" Then         'A - Absence
                    UserForm1.lbl0409.Caption = Worksheets("Report Calc").Cells(i, 62)
                    UserForm1.lbl0409.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 62) = "R" Then          'R - Rest Day
                    UserForm1.lbl0409.Caption = Worksheets("Report Calc").Cells(i, 62)
                    UserForm1.lbl0409.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0409.Caption = ""
        End If
                   
        If Worksheets("Report Calc").Cells(i, 63) = "0" Then                 '0 - Date
                    UserForm1.lbl0410.Caption = Worksheets("Report Calc").Cells(38, 63)
                    UserForm1.lbl0410.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 63) = "H" Then         'H - Holiday
                    UserForm1.lbl0410.Caption = Worksheets("Report Calc").Cells(i, 63)
                    UserForm1.lbl0410.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 63) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0410.Caption = Worksheets("Report Calc").Cells(i, 63)
                    UserForm1.lbl0410.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 63) = "L" Then         'L - Leave
                    UserForm1.lbl0410.Caption = Worksheets("Report Calc").Cells(i, 63)
                    UserForm1.lbl0410.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 63) = "A" Then         'A - Absence
                    UserForm1.lbl0410.Caption = Worksheets("Report Calc").Cells(i, 63)
                    UserForm1.lbl0410.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 63) = "R" Then          'R - Rest Day
                    UserForm1.lbl0410.Caption = Worksheets("Report Calc").Cells(i, 63)
                    UserForm1.lbl0410.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0410.Caption = ""
        End If
                   
        If Worksheets("Report Calc").Cells(i, 64) = "0" Then                 '0 - Date
                    UserForm1.lbl0411.Caption = Worksheets("Report Calc").Cells(38, 64)
                    UserForm1.lbl0411.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 64) = "H" Then         'H - Holiday
                    UserForm1.lbl0411.Caption = Worksheets("Report Calc").Cells(i, 64)
                    UserForm1.lbl0411.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 64) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0411.Caption = Worksheets("Report Calc").Cells(i, 64)
                    UserForm1.lbl0411.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 64) = "L" Then         'L - Leave
                    UserForm1.lbl0411.Caption = Worksheets("Report Calc").Cells(i, 64)
                    UserForm1.lbl0411.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 64) = "A" Then         'A - Absence
                    UserForm1.lbl0411.Caption = Worksheets("Report Calc").Cells(i, 64)
                    UserForm1.lbl0411.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 64) = "R" Then          'R - Rest Day
                    UserForm1.lbl0411.Caption = Worksheets("Report Calc").Cells(i, 64)
                    UserForm1.lbl0411.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0411.Caption = ""
        End If
                   
        If Worksheets("Report Calc").Cells(i, 65) = "0" Then                 '0 - Date
                    UserForm1.lbl0412.Caption = Worksheets("Report Calc").Cells(38, 65)
                    UserForm1.lbl0412.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 65) = "H" Then         'H - Holiday
                    UserForm1.lbl0412.Caption = Worksheets("Report Calc").Cells(i, 65)
                    UserForm1.lbl0412.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 65) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0412.Caption = Worksheets("Report Calc").Cells(i, 65)
                    UserForm1.lbl0412.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 65) = "L" Then         'L - Leave
                    UserForm1.lbl0412.Caption = Worksheets("Report Calc").Cells(i, 65)
                    UserForm1.lbl0412.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 65) = "A" Then         'A - Absence
                    UserForm1.lbl0412.Caption = Worksheets("Report Calc").Cells(i, 65)
                    UserForm1.lbl0412.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 65) = "R" Then          'R - Rest Day
                    UserForm1.lbl0412.Caption = Worksheets("Report Calc").Cells(i, 65)
                    UserForm1.lbl0412.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0412.Caption = ""
        End If
                   
                        
        If Worksheets("Report Calc").Cells(i, 66) = "0" Then                 '0 - Date
                    UserForm1.lbl0413.Caption = Worksheets("Report Calc").Cells(38, 66)
                    UserForm1.lbl0413.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 66) = "H" Then         'H - Holiday
                    UserForm1.lbl0413.Caption = Worksheets("Report Calc").Cells(i, 66)
                    UserForm1.lbl0413.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 66) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0413.Caption = Worksheets("Report Calc").Cells(i, 66)
                    UserForm1.lbl0413.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 66) = "L" Then         'L - Leave
                    UserForm1.lbl0413.Caption = Worksheets("Report Calc").Cells(i, 66)
                    UserForm1.lbl0413.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 66) = "A" Then         'A - Absence
                    UserForm1.lbl0413.Caption = Worksheets("Report Calc").Cells(i, 66)
                    UserForm1.lbl0413.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 66) = "R" Then          'R - Rest Day
                    UserForm1.lbl0413.Caption = Worksheets("Report Calc").Cells(i, 66)
                    UserForm1.lbl0413.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0413.Caption = ""
        End If
                   
                        
         If Worksheets("Report Calc").Cells(i, 67) = "0" Then                 '0 - Date
                    UserForm1.lbl0414.Caption = Worksheets("Report Calc").Cells(38, 67)
                    UserForm1.lbl0414.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 67) = "H" Then         'H - Holiday
                    UserForm1.lbl0414.Caption = Worksheets("Report Calc").Cells(i, 67)
                    UserForm1.lbl0414.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 67) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0414.Caption = Worksheets("Report Calc").Cells(i, 67)
                    UserForm1.lbl0414.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 67) = "L" Then         'L - Leave
                    UserForm1.lbl0414.Caption = Worksheets("Report Calc").Cells(i, 67)
                    UserForm1.lbl0414.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 67) = "A" Then         'A - Absence
                    UserForm1.lbl0414.Caption = Worksheets("Report Calc").Cells(i, 67)
                    UserForm1.lbl0414.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 67) = "R" Then          'R - Rest Day
                    UserForm1.lbl0414.Caption = Worksheets("Report Calc").Cells(i, 67)
                    UserForm1.lbl0414.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0414.Caption = ""
        End If
                                            
                                            
         If Worksheets("Report Calc").Cells(i, 68) = "0" Then                 '0 - Date
                    UserForm1.lbl0415.Caption = Worksheets("Report Calc").Cells(38, 68)
                    UserForm1.lbl0415.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 68) = "H" Then         'H - Holiday
                    UserForm1.lbl0415.Caption = Worksheets("Report Calc").Cells(i, 68)
                    UserForm1.lbl0415.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 68) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0415.Caption = Worksheets("Report Calc").Cells(i, 68)
                    UserForm1.lbl0415.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 68) = "L" Then         'L - Leave
                    UserForm1.lbl0415.Caption = Worksheets("Report Calc").Cells(i, 68)
                    UserForm1.lbl0415.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 68) = "A" Then         'A - Absence
                    UserForm1.lbl0415.Caption = Worksheets("Report Calc").Cells(i, 68)
                    UserForm1.lbl0415.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 68) = "R" Then          'R - Rest Day
                    UserForm1.lbl0415.Caption = Worksheets("Report Calc").Cells(i, 68)
                    UserForm1.lbl0415.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0415.Caption = ""
        End If
                   
                        
         If Worksheets("Report Calc").Cells(i, 69) = "0" Then                 '0 - Date
                    UserForm1.lbl0416.Caption = Worksheets("Report Calc").Cells(38, 69)
                    UserForm1.lbl0416.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 69) = "H" Then         'H - Holiday
                    UserForm1.lbl0416.Caption = Worksheets("Report Calc").Cells(i, 69)
                    UserForm1.lbl0416.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 69) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0416.Caption = Worksheets("Report Calc").Cells(i, 69)
                    UserForm1.lbl0416.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 69) = "L" Then         'L - Leave
                    UserForm1.lbl0416.Caption = Worksheets("Report Calc").Cells(i, 69)
                    UserForm1.lbl0416.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 69) = "A" Then         'A - Absence
                    UserForm1.lbl0416.Caption = Worksheets("Report Calc").Cells(i, 69)
                    UserForm1.lbl0416.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 69) = "R" Then          'R - Rest Day
                    UserForm1.lbl0416.Caption = Worksheets("Report Calc").Cells(i, 69)
                    UserForm1.lbl0416.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0416.Caption = ""
        End If
                   
                                            
         If Worksheets("Report Calc").Cells(i, 70) = "0" Then                 '0 - Date
                    UserForm1.lbl0417.Caption = Worksheets("Report Calc").Cells(38, 70)
                    UserForm1.lbl0417.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 70) = "H" Then         'H - Holiday
                    UserForm1.lbl0417.Caption = Worksheets("Report Calc").Cells(i, 70)
                    UserForm1.lbl0417.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 70) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0417.Caption = Worksheets("Report Calc").Cells(i, 70)
                    UserForm1.lbl0417.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 70) = "L" Then         'L - Leave
                    UserForm1.lbl0417.Caption = Worksheets("Report Calc").Cells(i, 70)
                    UserForm1.lbl0417.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 70) = "A" Then         'A - Absence
                    UserForm1.lbl0417.Caption = Worksheets("Report Calc").Cells(i, 70)
                    UserForm1.lbl0417.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 70) = "R" Then          'R - Rest Day
                    UserForm1.lbl0417.Caption = Worksheets("Report Calc").Cells(i, 70)
                    UserForm1.lbl0417.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0417.Caption = ""
        End If
                   
                                 
         If Worksheets("Report Calc").Cells(i, 71) = "0" Then                 '0 - Date
                    UserForm1.lbl0418.Caption = Worksheets("Report Calc").Cells(38, 71)
                    UserForm1.lbl0418.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 71) = "H" Then         'H - Holiday
                    UserForm1.lbl0418.Caption = Worksheets("Report Calc").Cells(i, 71)
                    UserForm1.lbl0418.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 71) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0418.Caption = Worksheets("Report Calc").Cells(i, 71)
                    UserForm1.lbl0418.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 71) = "L" Then         'L - Leave
                    UserForm1.lbl0418.Caption = Worksheets("Report Calc").Cells(i, 71)
                    UserForm1.lbl0418.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 71) = "A" Then         'A - Absence
                    UserForm1.lbl0418.Caption = Worksheets("Report Calc").Cells(i, 71)
                    UserForm1.lbl0418.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 71) = "R" Then          'R - Rest Day
                    UserForm1.lbl0418.Caption = Worksheets("Report Calc").Cells(i, 71)
                    UserForm1.lbl0418.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0418.Caption = ""
        End If
                   
                                 
         If Worksheets("Report Calc").Cells(i, 72) = "0" Then                 '0 - Date
                    UserForm1.lbl0419.Caption = Worksheets("Report Calc").Cells(38, 72)
                    UserForm1.lbl0419.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 72) = "H" Then         'H - Holiday
                    UserForm1.lbl0419.Caption = Worksheets("Report Calc").Cells(i, 72)
                    UserForm1.lbl0419.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 72) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0419.Caption = Worksheets("Report Calc").Cells(i, 72)
                    UserForm1.lbl0419.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 72) = "L" Then         'L - Leave
                    UserForm1.lbl0419.Caption = Worksheets("Report Calc").Cells(i, 72)
                    UserForm1.lbl0419.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 72) = "A" Then         'A - Absence
                    UserForm1.lbl0419.Caption = Worksheets("Report Calc").Cells(i, 72)
                    UserForm1.lbl0419.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 72) = "R" Then          'R - Rest Day
                    UserForm1.lbl0419.Caption = Worksheets("Report Calc").Cells(i, 72)
                    UserForm1.lbl0419.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0419.Caption = ""
        End If
        
        
         If Worksheets("Report Calc").Cells(i, 73) = "0" Then                 '0 - Date
                    UserForm1.lbl0420.Caption = Worksheets("Report Calc").Cells(38, 73)
                    UserForm1.lbl0420.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 73) = "H" Then         'H - Holiday
                    UserForm1.lbl0420.Caption = Worksheets("Report Calc").Cells(i, 73)
                    UserForm1.lbl0420.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 73) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0420.Caption = Worksheets("Report Calc").Cells(i, 73)
                    UserForm1.lbl0420.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 73) = "L" Then         'L - Leave
                    UserForm1.lbl0420.Caption = Worksheets("Report Calc").Cells(i, 73)
                    UserForm1.lbl0420.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 73) = "A" Then         'A - Absence
                    UserForm1.lbl0420.Caption = Worksheets("Report Calc").Cells(i, 73)
                    UserForm1.lbl0420.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 73) = "R" Then          'R - Rest Day
                    UserForm1.lbl0420.Caption = Worksheets("Report Calc").Cells(i, 73)
                    UserForm1.lbl0420.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0420.Caption = ""
        End If
        
                
         If Worksheets("Report Calc").Cells(i, 74) = "0" Then                 '0 - Date
                    UserForm1.lbl0421.Caption = Worksheets("Report Calc").Cells(38, 74)
                    UserForm1.lbl0421.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 74) = "H" Then         'H - Holiday
                    UserForm1.lbl0421.Caption = Worksheets("Report Calc").Cells(i, 74)
                    UserForm1.lbl0421.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 74) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0421.Caption = Worksheets("Report Calc").Cells(i, 74)
                    UserForm1.lbl0421.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 74) = "L" Then         'L - Leave
                    UserForm1.lbl0421.Caption = Worksheets("Report Calc").Cells(i, 74)
                    UserForm1.lbl0421.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 74) = "A" Then         'A - Absence
                    UserForm1.lbl0421.Caption = Worksheets("Report Calc").Cells(i, 74)
                    UserForm1.lbl0421.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 74) = "R" Then          'R - Rest Day
                    UserForm1.lbl0421.Caption = Worksheets("Report Calc").Cells(i, 74)
                    UserForm1.lbl0421.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0421.Caption = ""
        End If
        
                
         If Worksheets("Report Calc").Cells(i, 75) = "0" Then                 '0 - Date
                    UserForm1.lbl0422.Caption = Worksheets("Report Calc").Cells(38, 75)
                    UserForm1.lbl0422.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 75) = "H" Then         'H - Holiday
                    UserForm1.lbl0422.Caption = Worksheets("Report Calc").Cells(i, 75)
                    UserForm1.lbl0422.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 75) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0422.Caption = Worksheets("Report Calc").Cells(i, 75)
                    UserForm1.lbl0422.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 75) = "L" Then         'L - Leave
                    UserForm1.lbl0422.Caption = Worksheets("Report Calc").Cells(i, 75)
                    UserForm1.lbl0422.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 75) = "A" Then         'A - Absence
                    UserForm1.lbl0422.Caption = Worksheets("Report Calc").Cells(i, 75)
                    UserForm1.lbl0422.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 75) = "R" Then          'R - Rest Day
                    UserForm1.lbl0422.Caption = Worksheets("Report Calc").Cells(i, 75)
                    UserForm1.lbl0422.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0422.Caption = ""
        End If
        
                
         If Worksheets("Report Calc").Cells(i, 76) = "0" Then                 '0 - Date
                    UserForm1.lbl0423.Caption = Worksheets("Report Calc").Cells(38, 76)
                    UserForm1.lbl0423.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 76) = "H" Then         'H - Holiday
                    UserForm1.lbl0423.Caption = Worksheets("Report Calc").Cells(i, 76)
                    UserForm1.lbl0423.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 76) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0423.Caption = Worksheets("Report Calc").Cells(i, 76)
                    UserForm1.lbl0423.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 76) = "L" Then         'L - Leave
                    UserForm1.lbl0423.Caption = Worksheets("Report Calc").Cells(i, 76)
                    UserForm1.lbl0423.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 76) = "A" Then         'A - Absence
                    UserForm1.lbl0423.Caption = Worksheets("Report Calc").Cells(i, 76)
                    UserForm1.lbl0423.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 76) = "R" Then          'R - Rest Day
                    UserForm1.lbl0423.Caption = Worksheets("Report Calc").Cells(i, 76)
                    UserForm1.lbl0423.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0423.Caption = ""
        End If
        
                
         If Worksheets("Report Calc").Cells(i, 77) = "0" Then                 '0 - Date
                    UserForm1.lbl0424.Caption = Worksheets("Report Calc").Cells(38, 77)
                    UserForm1.lbl0424.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 77) = "H" Then         'H - Holiday
                    UserForm1.lbl0424.Caption = Worksheets("Report Calc").Cells(i, 77)
                    UserForm1.lbl0424.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 77) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0424.Caption = Worksheets("Report Calc").Cells(i, 77)
                    UserForm1.lbl0424.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 77) = "L" Then         'L - Leave
                    UserForm1.lbl0424.Caption = Worksheets("Report Calc").Cells(i, 77)
                    UserForm1.lbl0424.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 77) = "A" Then         'A - Absence
                    UserForm1.lbl0424.Caption = Worksheets("Report Calc").Cells(i, 77)
                    UserForm1.lbl0424.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 77) = "R" Then          'R - Rest Day
                    UserForm1.lbl0424.Caption = Worksheets("Report Calc").Cells(i, 77)
                    UserForm1.lbl0424.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0424.Caption = ""
        End If
        
       
         If Worksheets("Report Calc").Cells(i, 78) = "0" Then                 '0 - Date
                    UserForm1.lbl0425.Caption = Worksheets("Report Calc").Cells(38, 78)
                    UserForm1.lbl0425.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 78) = "H" Then         'H - Holiday
                    UserForm1.lbl0425.Caption = Worksheets("Report Calc").Cells(i, 78)
                    UserForm1.lbl0425.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 78) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0425.Caption = Worksheets("Report Calc").Cells(i, 78)
                    UserForm1.lbl0425.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 78) = "L" Then         'L - Leave
                    UserForm1.lbl0425.Caption = Worksheets("Report Calc").Cells(i, 78)
                    UserForm1.lbl0425.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 78) = "A" Then         'A - Absence
                    UserForm1.lbl0425.Caption = Worksheets("Report Calc").Cells(i, 78)
                    UserForm1.lbl0425.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 78) = "R" Then          'R - Rest Day
                    UserForm1.lbl0425.Caption = Worksheets("Report Calc").Cells(i, 78)
                    UserForm1.lbl0425.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0425.Caption = ""
        End If
        
       
         If Worksheets("Report Calc").Cells(i, 79) = "0" Then                 '0 - Date
                    UserForm1.lbl0426.Caption = Worksheets("Report Calc").Cells(38, 79)
                    UserForm1.lbl0426.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 79) = "H" Then         'H - Holiday
                    UserForm1.lbl0426.Caption = Worksheets("Report Calc").Cells(i, 79)
                    UserForm1.lbl0426.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 79) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0426.Caption = Worksheets("Report Calc").Cells(i, 79)
                    UserForm1.lbl0426.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 79) = "L" Then         'L - Leave
                    UserForm1.lbl0426.Caption = Worksheets("Report Calc").Cells(i, 79)
                    UserForm1.lbl0426.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 79) = "A" Then         'A - Absence
                    UserForm1.lbl0426.Caption = Worksheets("Report Calc").Cells(i, 79)
                    UserForm1.lbl0426.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 79) = "R" Then          'R - Rest Day
                    UserForm1.lbl0426.Caption = Worksheets("Report Calc").Cells(i, 79)
                    UserForm1.lbl0426.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0426.Caption = ""
        End If
        
       
         If Worksheets("Report Calc").Cells(i, 80) = "0" Then                 '0 - Date
                    UserForm1.lbl0427.Caption = Worksheets("Report Calc").Cells(38, 80)
                    UserForm1.lbl0427.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 80) = "H" Then         'H - Holiday
                    UserForm1.lbl0427.Caption = Worksheets("Report Calc").Cells(i, 80)
                    UserForm1.lbl0427.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 80) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0427.Caption = Worksheets("Report Calc").Cells(i, 80)
                    UserForm1.lbl0427.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 80) = "L" Then         'L - Leave
                    UserForm1.lbl0427.Caption = Worksheets("Report Calc").Cells(i, 80)
                    UserForm1.lbl0427.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 80) = "A" Then         'A - Absence
                    UserForm1.lbl0427.Caption = Worksheets("Report Calc").Cells(i, 80)
                    UserForm1.lbl0427.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 80) = "R" Then          'R - Rest Day
                    UserForm1.lbl0427.Caption = Worksheets("Report Calc").Cells(i, 80)
                    UserForm1.lbl0427.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0427.Caption = ""
        End If
        
       
         If Worksheets("Report Calc").Cells(i, 81) = "0" Then                 '0 - Date
                    UserForm1.lbl0428.Caption = Worksheets("Report Calc").Cells(38, 81)
                    UserForm1.lbl0428.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 81) = "H" Then         'H - Holiday
                    UserForm1.lbl0428.Caption = Worksheets("Report Calc").Cells(i, 81)
                    UserForm1.lbl0428.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 81) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0428.Caption = Worksheets("Report Calc").Cells(i, 81)
                    UserForm1.lbl0428.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 81) = "L" Then         'L - Leave
                    UserForm1.lbl0428.Caption = Worksheets("Report Calc").Cells(i, 81)
                    UserForm1.lbl0428.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 81) = "A" Then         'A - Absence
                    UserForm1.lbl0428.Caption = Worksheets("Report Calc").Cells(i, 81)
                    UserForm1.lbl0428.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 81) = "R" Then          'R - Rest Day
                    UserForm1.lbl0428.Caption = Worksheets("Report Calc").Cells(i, 81)
                    UserForm1.lbl0428.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0428.Caption = ""
        End If
        
       
         If Worksheets("Report Calc").Cells(i, 82) = "0" Then                 '0 - Date
                    UserForm1.lbl0429.Caption = Worksheets("Report Calc").Cells(38, 82)
                    UserForm1.lbl0429.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 82) = "H" Then         'H - Holiday
                    UserForm1.lbl0429.Caption = Worksheets("Report Calc").Cells(i, 82)
                    UserForm1.lbl0429.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 82) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0429.Caption = Worksheets("Report Calc").Cells(i, 82)
                    UserForm1.lbl0429.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 82) = "L" Then         'L - Leave
                    UserForm1.lbl0429.Caption = Worksheets("Report Calc").Cells(i, 82)
                    UserForm1.lbl0429.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 82) = "A" Then         'A - Absence
                    UserForm1.lbl0429.Caption = Worksheets("Report Calc").Cells(i, 82)
                    UserForm1.lbl0429.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 82) = "R" Then          'R - Rest Day
                    UserForm1.lbl0429.Caption = Worksheets("Report Calc").Cells(i, 82)
                    UserForm1.lbl0429.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0429.Caption = ""
        End If
            

         If Worksheets("Report Calc").Cells(i, 83) = "0" Then                 '0 - Date
                    UserForm1.lbl0430.Caption = Worksheets("Report Calc").Cells(38, 83)
                    UserForm1.lbl0430.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 83) = "H" Then         'H - Holiday
                    UserForm1.lbl0430.Caption = Worksheets("Report Calc").Cells(i, 83)
                    UserForm1.lbl0430.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 83) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0430.Caption = Worksheets("Report Calc").Cells(i, 83)
                    UserForm1.lbl0430.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 83) = "L" Then         'L - Leave
                    UserForm1.lbl0430.Caption = Worksheets("Report Calc").Cells(i, 83)
                    UserForm1.lbl0430.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 83) = "A" Then         'A - Absence
                    UserForm1.lbl0430.Caption = Worksheets("Report Calc").Cells(i, 83)
                    UserForm1.lbl0430.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 83) = "R" Then          'R - Rest Day
                    UserForm1.lbl0430.Caption = Worksheets("Report Calc").Cells(i, 83)
                    UserForm1.lbl0430.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0430.Caption = ""
        End If


         If Worksheets("Report Calc").Cells(i, 84) = "0" Then                 '0 - Date
                    UserForm1.lbl0431.Caption = Worksheets("Report Calc").Cells(38, 84)
                    UserForm1.lbl0431.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 84) = "H" Then         'H - Holiday
                    UserForm1.lbl0431.Caption = Worksheets("Report Calc").Cells(i, 84)
                    UserForm1.lbl0431.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 84) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0431.Caption = Worksheets("Report Calc").Cells(i, 84)
                    UserForm1.lbl0431.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 84) = "L" Then         'L - Leave
                    UserForm1.lbl0431.Caption = Worksheets("Report Calc").Cells(i, 84)
                    UserForm1.lbl0431.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 84) = "A" Then         'A - Absence
                    UserForm1.lbl0431.Caption = Worksheets("Report Calc").Cells(i, 84)
                    UserForm1.lbl0431.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 84) = "R" Then          'R - Rest Day
                    UserForm1.lbl0431.Caption = Worksheets("Report Calc").Cells(i, 84)
                    UserForm1.lbl0431.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0431.Caption = ""
        End If


         If Worksheets("Report Calc").Cells(i, 85) = "0" Then                 '0 - Date
                    UserForm1.lbl0432.Caption = Worksheets("Report Calc").Cells(38, 85)
                    UserForm1.lbl0432.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 85) = "H" Then         'H - Holiday
                    UserForm1.lbl0432.Caption = Worksheets("Report Calc").Cells(i, 85)
                    UserForm1.lbl0432.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 85) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0432.Caption = Worksheets("Report Calc").Cells(i, 85)
                    UserForm1.lbl0432.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 85) = "L" Then         'L - Leave
                    UserForm1.lbl0432.Caption = Worksheets("Report Calc").Cells(i, 85)
                    UserForm1.lbl0432.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 85) = "A" Then         'A - Absence
                    UserForm1.lbl0432.Caption = Worksheets("Report Calc").Cells(i, 85)
                    UserForm1.lbl0432.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 85) = "R" Then          'R - Rest Day
                    UserForm1.lbl0432.Caption = Worksheets("Report Calc").Cells(i, 85)
                    UserForm1.lbl0432.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0432.Caption = ""
        End If


         If Worksheets("Report Calc").Cells(i, 86) = "0" Then                 '0 - Date
                    UserForm1.lbl0433.Caption = Worksheets("Report Calc").Cells(38, 86)
                    UserForm1.lbl0433.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 86) = "H" Then         'H - Holiday
                    UserForm1.lbl0433.Caption = Worksheets("Report Calc").Cells(i, 86)
                    UserForm1.lbl0433.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 86) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0433.Caption = Worksheets("Report Calc").Cells(i, 86)
                    UserForm1.lbl0433.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 86) = "L" Then         'L - Leave
                    UserForm1.lbl0433.Caption = Worksheets("Report Calc").Cells(i, 86)
                    UserForm1.lbl0433.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 86) = "A" Then         'A - Absence
                    UserForm1.lbl0433.Caption = Worksheets("Report Calc").Cells(i, 86)
                    UserForm1.lbl0433.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 86) = "R" Then          'R - Rest Day
                    UserForm1.lbl0433.Caption = Worksheets("Report Calc").Cells(i, 86)
                    UserForm1.lbl0433.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0433.Caption = ""
        End If


         If Worksheets("Report Calc").Cells(i, 87) = "0" Then                 '0 - Date
                    UserForm1.lbl0434.Caption = Worksheets("Report Calc").Cells(38, 87)
                    UserForm1.lbl0434.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 87) = "H" Then         'H - Holiday
                    UserForm1.lbl0434.Caption = Worksheets("Report Calc").Cells(i, 87)
                    UserForm1.lbl0434.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 87) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0434.Caption = Worksheets("Report Calc").Cells(i, 87)
                    UserForm1.lbl0434.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 87) = "L" Then         'L - Leave
                    UserForm1.lbl0434.Caption = Worksheets("Report Calc").Cells(i, 87)
                    UserForm1.lbl0434.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 87) = "A" Then         'A - Absence
                    UserForm1.lbl0434.Caption = Worksheets("Report Calc").Cells(i, 87)
                    UserForm1.lbl0434.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 87) = "R" Then          'R - Rest Day
                    UserForm1.lbl0434.Caption = Worksheets("Report Calc").Cells(i, 87)
                    UserForm1.lbl0434.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0434.Caption = ""
        End If


         If Worksheets("Report Calc").Cells(i, 88) = "0" Then                 '0 - Date
                    UserForm1.lbl0435.Caption = Worksheets("Report Calc").Cells(38, 88)
                    UserForm1.lbl0435.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 88) = "H" Then         'H - Holiday
                    UserForm1.lbl0435.Caption = Worksheets("Report Calc").Cells(i, 88)
                    UserForm1.lbl0435.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 88) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0435.Caption = Worksheets("Report Calc").Cells(i, 88)
                    UserForm1.lbl0435.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 88) = "L" Then         'L - Leave
                    UserForm1.lbl0435.Caption = Worksheets("Report Calc").Cells(i, 88)
                    UserForm1.lbl0435.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 88) = "A" Then         'A - Absence
                    UserForm1.lbl0435.Caption = Worksheets("Report Calc").Cells(i, 88)
                    UserForm1.lbl0435.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 88) = "R" Then          'R - Rest Day
                    UserForm1.lbl0435.Caption = Worksheets("Report Calc").Cells(i, 88)
                    UserForm1.lbl0435.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0435.Caption = ""
        End If


         If Worksheets("Report Calc").Cells(i, 89) = "0" Then                 '0 - Date
                    UserForm1.lbl0436.Caption = Worksheets("Report Calc").Cells(38, 89)
                    UserForm1.lbl0436.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 89) = "H" Then         'H - Holiday
                    UserForm1.lbl0436.Caption = Worksheets("Report Calc").Cells(i, 89)
                    UserForm1.lbl0436.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 89) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0436.Caption = Worksheets("Report Calc").Cells(i, 89)
                    UserForm1.lbl0436.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 89) = "L" Then         'L - Leave
                    UserForm1.lbl0436.Caption = Worksheets("Report Calc").Cells(i, 89)
                    UserForm1.lbl0436.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 89) = "A" Then         'A - Absence
                    UserForm1.lbl0436.Caption = Worksheets("Report Calc").Cells(i, 89)
                    UserForm1.lbl0436.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 89) = "R" Then          'R - Rest Day
                    UserForm1.lbl0436.Caption = Worksheets("Report Calc").Cells(i, 89)
                    UserForm1.lbl0436.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0436.Caption = ""
        End If



         If Worksheets("Report Calc").Cells(i, 90) = "0" Then                 '0 - Date
                    UserForm1.lbl0437.Caption = Worksheets("Report Calc").Cells(38, 90)
                    UserForm1.lbl0437.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 90) = "H" Then         'H - Holiday
                    UserForm1.lbl0437.Caption = Worksheets("Report Calc").Cells(i, 90)
                    UserForm1.lbl0437.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 90) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0437.Caption = Worksheets("Report Calc").Cells(i, 90)
                    UserForm1.lbl0437.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 90) = "L" Then         'L - Leave
                    UserForm1.lbl0437.Caption = Worksheets("Report Calc").Cells(i, 90)
                    UserForm1.lbl0437.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 90) = "A" Then         'A - Absence
                    UserForm1.lbl0437.Caption = Worksheets("Report Calc").Cells(i, 90)
                    UserForm1.lbl0437.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 90) = "R" Then          'R - Rest Day
                    UserForm1.lbl0437.Caption = Worksheets("Report Calc").Cells(i, 90)
                    UserForm1.lbl0437.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0437.Caption = ""
        End If


         If Worksheets("Report Calc").Cells(i, 91) = "0" Then                 '0 - Date
                    UserForm1.lbl0438.Caption = Worksheets("Report Calc").Cells(38, 91)
                    UserForm1.lbl0438.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 91) = "H" Then         'H - Holiday
                    UserForm1.lbl0438.Caption = Worksheets("Report Calc").Cells(i, 91)
                    UserForm1.lbl0438.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 91) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0438.Caption = Worksheets("Report Calc").Cells(i, 91)
                    UserForm1.lbl0438.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 91) = "L" Then         'L - Leave
                    UserForm1.lbl0438.Caption = Worksheets("Report Calc").Cells(i, 91)
                    UserForm1.lbl0438.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 91) = "A" Then         'A - Absence
                    UserForm1.lbl0438.Caption = Worksheets("Report Calc").Cells(i, 91)
                    UserForm1.lbl0438.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 91) = "R" Then          'R - Rest Day
                    UserForm1.lbl0438.Caption = Worksheets("Report Calc").Cells(i, 91)
                    UserForm1.lbl0438.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0438.Caption = ""
        End If


         If Worksheets("Report Calc").Cells(i, 92) = "0" Then                 '0 - Date
                    UserForm1.lbl0439.Caption = Worksheets("Report Calc").Cells(38, 92)
                    UserForm1.lbl0439.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 92) = "H" Then         'H - Holiday
                    UserForm1.lbl0439.Caption = Worksheets("Report Calc").Cells(i, 92)
                    UserForm1.lbl0439.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 92) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0439.Caption = Worksheets("Report Calc").Cells(i, 92)
                    UserForm1.lbl0439.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 92) = "L" Then         'L - Leave
                    UserForm1.lbl0439.Caption = Worksheets("Report Calc").Cells(i, 92)
                    UserForm1.lbl0439.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 92) = "A" Then         'A - Absence
                    UserForm1.lbl0439.Caption = Worksheets("Report Calc").Cells(i, 92)
                    UserForm1.lbl0439.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 92) = "R" Then          'R - Rest Day
                    UserForm1.lbl0439.Caption = Worksheets("Report Calc").Cells(i, 92)
                    UserForm1.lbl0439.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0439.Caption = ""
        End If


         If Worksheets("Report Calc").Cells(i, 93) = "0" Then                 '0 - Date
                    UserForm1.lbl0440.Caption = Worksheets("Report Calc").Cells(38, 93)
                    UserForm1.lbl0440.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 93) = "H" Then         'H - Holiday
                    UserForm1.lbl0440.Caption = Worksheets("Report Calc").Cells(i, 93)
                    UserForm1.lbl0440.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 93) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0440.Caption = Worksheets("Report Calc").Cells(i, 93)
                    UserForm1.lbl0440.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 93) = "L" Then         'L - Leave
                    UserForm1.lbl0440.Caption = Worksheets("Report Calc").Cells(i, 93)
                    UserForm1.lbl0440.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 93) = "A" Then         'A - Absence
                    UserForm1.lbl0440.Caption = Worksheets("Report Calc").Cells(i, 93)
                    UserForm1.lbl0440.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 93) = "R" Then          'R - Rest Day
                    UserForm1.lbl0440.Caption = Worksheets("Report Calc").Cells(i, 93)
                    UserForm1.lbl0440.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0440.Caption = ""
        End If


         If Worksheets("Report Calc").Cells(i, 94) = "0" Then                 '0 - Date
                    UserForm1.lbl0441.Caption = Worksheets("Report Calc").Cells(38, 94)
                    UserForm1.lbl0441.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 94) = "H" Then         'H - Holiday
                    UserForm1.lbl0441.Caption = Worksheets("Report Calc").Cells(i, 94)
                    UserForm1.lbl0441.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 94) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0441.Caption = Worksheets("Report Calc").Cells(i, 94)
                    UserForm1.lbl0441.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 94) = "L" Then         'L - Leave
                    UserForm1.lbl0441.Caption = Worksheets("Report Calc").Cells(i, 94)
                    UserForm1.lbl0441.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 94) = "A" Then         'A - Absence
                    UserForm1.lbl0441.Caption = Worksheets("Report Calc").Cells(i, 94)
                    UserForm1.lbl0441.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 94) = "R" Then          'R - Rest Day
                    UserForm1.lbl0441.Caption = Worksheets("Report Calc").Cells(i, 94)
                    UserForm1.lbl0441.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0441.Caption = ""
        End If


         If Worksheets("Report Calc").Cells(i, 95) = "0" Then                 '0 - Date
                    UserForm1.lbl0442.Caption = Worksheets("Report Calc").Cells(38, 95)
                    UserForm1.lbl0442.BackColor = RGB(255, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 95) = "H" Then         'H - Holiday
                    UserForm1.lbl0442.Caption = Worksheets("Report Calc").Cells(i, 95)
                    UserForm1.lbl0442.BackColor = RGB(255, 255, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 95) = "BH" Then        'BH - Bank Hol
                    UserForm1.lbl0442.Caption = Worksheets("Report Calc").Cells(i, 95)
                    UserForm1.lbl0442.BackColor = RGB(255, 153, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 95) = "L" Then         'L - Leave
                    UserForm1.lbl0442.Caption = Worksheets("Report Calc").Cells(i, 95)
                    UserForm1.lbl0442.BackColor = RGB(204, 255, 255)
            ElseIf Worksheets("Report Calc").Cells(i, 95) = "A" Then         'A - Absence
                    UserForm1.lbl0442.Caption = Worksheets("Report Calc").Cells(i, 95)
                    UserForm1.lbl0442.BackColor = RGB(255, 0, 0)
            ElseIf Worksheets("Report Calc").Cells(i, 95) = "R" Then          'R - Rest Day
                    UserForm1.lbl0442.Caption = Worksheets("Report Calc").Cells(i, 95)
                    UserForm1.lbl0442.BackColor = RGB(150, 150, 150)
            Else
                    UserForm1.lbl0442.Caption = ""
        End If
Next
End Sub
 
Sort by date Sort by votes
Fluff said:
Ok, try this
VBA Code: 
Sub FormApril()
' ----------------------------------------- April -----------------------------------------------------
   Dim totRows As Long, i As Long, j As Long
   Dim Ws As Worksheet
 
   Set Ws = Worksheets("Report Calc")
   totRows = Ws.Range("AS38").CurrentRegion.Rows.Count
   For i = 39 To totRows
      If Ws.Cells(i, 45).Value = Val(UserForm1.TextBox1) Then
         For j = 54 To 95
            With UserForm1.Controls("lbl04" & Format(j - 53, "00"))
                If Ws.Cells(i, j) = "0" Then                 '0 - Date
                   .Caption = Ws.Cells(38, j) '54
                   .BackColor = RGB(255, 255, 255)
                ElseIf Ws.Cells(i, j) = "H" Then         'H - Holiday
                   .Caption = Ws.Cells(i, j)
                   .BackColor = RGB(255, 255, 0)
                ElseIf Ws.Cells(i, j) = "BH" Then        'BH - Bank Hol
                   .Caption = Ws.Cells(i, j)
                   .BackColor = RGB(255, 153, 0)
                ElseIf Ws.Cells(i, j) = "L" Then         'L - Leave
                   .Caption = Ws.Cells(i, j)
                   .BackColor = RGB(204, 255, 255)
                ElseIf .Cells(i, j) = "A" Then         'A - Absence
                   .Caption = Ws.Cells(i, j)
                   .BackColor = RGB(255, 0, 0)
                ElseIf Ws.Cells(i, j) = "R" Then          'R - Rest Day
                   .Caption = Ws.Cells(i, j)
                   .BackColor = RGB(150, 150, 150)
                Else
                   .Caption = ""
               End If
            End With
         Next j
         Exit For
      End If
   Next i
End Sub
Click to expand...
Second to last ElseIf threw up an error, quick fix, the "Ws." was missing before Cells.
Ran it again after I sorted that and it still isn't pulling through the "H" or any other values. Just the dates and the blank cells.
Much cleaner than my code though, well impressed! haha.

Hang on, I think I know why. One moment, let me try something....
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Fluff said:
Ok, try this
Click to expand...
Pritch558 said:
Hang on, I think I know why. One moment, let me try something....
Click to expand...

Apologies, my fault. When running the search command button in the userform it clears the textbox, but then displays the ID number (if found) in a label named ResultID. So in your code, I changed
VBA Code: 
If Ws.Cells(i, 45).Value = Val(UserForm1.TextBox1) Then
to
VBA Code: 
If Ws.Cells(i, 45).Value = Val(UserForm1.ResultID) Then

And it works a treat! Thank you so much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
How to shorten vba code without any problems
Replies
1
Views
288
kamranyd
K
E
  • Question Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
545
offthelip
O
M
  • Question Question
What am I doing Wrong?
Replies
6
Views
267
MBarrett
M
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
A
  • Question Question
Data Selection Based on Closest Value [20% Tolerance]
Replies
1
Views
210
StephenCrump
S

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top