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

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
 

Pritch558

New Member
Joined
Jan 30, 2015
Messages
18
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
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....
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Pritch558

New Member
Joined
Jan 30, 2015
Messages
18
Ok, try this
Hang on, I think I know why. One moment, let me try something....

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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Forum statistics

Threads
1,141,707
Messages
5,707,983
Members
421,539
Latest member
zuniBM

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
Top