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
As the first cell that contains a H seems to be col 63 add this line as shown
Rich (BB code): 
MsgBox "|" & Worksheets("Report Calc").Cells(i, 63) & "|" & vblf & i
If Worksheets("Report Calc").Cells(i, 63) = "0" Then
What does it say?
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
If it's a blank cell then it performs the "Else" statement, which works.
If it contains a "0" then it returns the value from row 38, which works.
If it contains any of the ElseIf statements, it doesn't work ?‍♂️
Click to expand...
It will always do the else statement and put blank , this is because there is nothing in any of the rows in column 54, so it never puts the values in from row 38 in ( according to the data displayed) the elseif statement is doing exactly what it should do , it NEVER triggers because the re is NOTHING in column 54
 
Upvote 0
As you are looping through the rows, the labels will only ever reflect the values in the last row.
 
Upvote 0
Fluff said:
As the first cell that contains a H seems to be col 63 add this line as shown
Rich (BB code): 
MsgBox "|" & Worksheets("Report Calc").Cells(i, 63) & "|" & vblf & i
If Worksheets("Report Calc").Cells(i, 63) = "0" Then
What does it say?
Click to expand...

That's worked. Generated the msg box showing H, interestingly its also displayed the correct return value in the applicable label? (image attached)

I don't understand what the 39 is?
 

Attachments

  • Untitled1.png
    Untitled1.png
    4.4 KB · Views: 8
Upvote 0
Fluff said:
As you are looping through the rows, the labels will only ever reflect the values in the last row.
Click to expand...
Hand in face.
Ofcourse, i've not told it what row it should stop.
How could I factor this in? So the search criteria is the ID number (Colum AS from 39 down). This is entered into a text box (TextBox1) on the userform (Userform1).
 
Upvote 0
Is col AS numbers as in the image?
 
Upvote 0
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
 
Upvote 0
Solution
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,179
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