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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Just a guess.... I would trial replacing the numeric column value in "Cells" with the actual column letters... just in case U haven't counted correctly. You can trial adding a few msgboxes just to see what if any values U are looking at...
Code: 
 Msgbox Worksheets("Report Calc").Cells(i, 54)
The elseif statements etc. seem to be okay. HTH. Dave
 
Upvote 0
Is that first section meant to be looking at col BB?
Also in what way doesn't it work?
 
Upvote 0
Hi Fluff.
Yes, first section looks at BB, then BC, then BD and so on.

It doesn't work as it doesn't seem to be doing the ElseIf parts:
VBA Code: 
 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)

It does the first part of the IF statement:
VBA Code: 
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)

And it does the ELSE:
VBA Code: 
Else
                    UserForm1.lbl0401.Caption = ""
        End If
 
Upvote 0
That suggest the cells do not have exactly H, BH, L, A or R
Remember that VBA is case sensitive.
 
Upvote 0
Fluff said:
That suggest the cells do not have exactly H, BH, L, A or R
Remember that VBA is case sensitive.
Click to expand...
I've attached a screenshot with the test data. Either i'm going mad or the letters definitely exist and cases match?
 

Attachments

  • Untitled.png
    Untitled.png
    77.3 KB · Views: 19
Upvote 0
Check for leading/trailing spaces.
 
Upvote 0
N
Fluff said:
Check for leading/trailing spaces.
Click to expand...
Nope, definitely no leading or trailing spaces either. That cell's value is retrieved from another sheet, and is based on a drop down. So H as an option in the dropdown, no spaces. Should I use TRIM in the code just in case?
 
Upvote 0
looking at your data column 54 doesn't have any of the letters that you are looking for , it has an "S" in column 37 and all the others are blank!! Were you trying to run across the worksheet?
 
Upvote 0
offthelip said:
looking at your data column 54 doesn't have any of the letters that you are looking for , it has an "S" in column 37 and all the others are blank!! Were you trying to run across the worksheet?
Click to expand...

Looking at rows 39 and down.
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 ?‍♂️
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
270
offthelip
O
E
  • 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
2K
profray
P
P
  • Question
The cell or chart you're trying to change is on a protected sheet. Error upon placing worksheet Change Selection Code
Replies
1
Views
4K
pingme89
P
Q
  • Solved
Error in VBA Sub Colors() lines of code
Replies
7
Views
1K
Fluff
Fluff
Ironman
  • Question
Review of Application.EnableEvents = False/True lines in worksheet_change event
Replies
1
Views
2K
jasonb75
J

Forum statistics

Threads
1,214,653
Messages
6,120,749
Members
448,989
Latest member
mariah3

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