Simplify this form code

[yasawa]

Hello
I have this code working on a form, it's working fine, simply writing the caption to the label in the form (from a cell value in sheet), then checking the offset cell value in the sheet to color the background label in the form
I tried using variables, using loops and many other methods to get the same result without having to repeat the lines over and over but none worked

Private Sub UserForm_Initialize()

Dim s As String, a As Integer, b As Integer, c As Integer, w

Me.Label1.Caption = CStr(ThisWorkbook.Sheets("Path").Range("A2").Value)
Me.Label2.Caption = CStr(ThisWorkbook.Sheets("Path").Range("A3").Value)
Me.Label3.Caption = CStr(ThisWorkbook.Sheets("Path").Range("A4").Value)
Me.Label4.Caption = CStr(ThisWorkbook.Sheets("Path").Range("A5").Value)


s = Sheets("Path").Range("A2").Offset(0, 1).Value
Select Case s
Case Is = "NO"
a = 255
b = 0
c = 0
Case Is = "OK"
a = 0
b = 255
c = 0
End Select
Me.Label1.BackColor = RGB(a, b, c)


s = Sheets("Path").Range("A3").Offset(0, 1).Value
Select Case s
Case Is = "NO"
a = 255
b = 0
c = 0
Case Is = "OK"
a = 0
b = 255
c = 0
End Select
Me.Label2.BackColor = RGB(a, b, c)


s = Sheets("Path").Range("A4").Offset(0, 1).Value
Select Case s
Case Is = "NO"
a = 255
b = 0
c = 0
Case Is = "OK"
a = 0
b = 255
c = 0
End Select
Me.Label3.BackColor = RGB(a, b, c)


s = Sheets("Path").Range("A5").Offset(0, 1).Value
Select Case s
Case Is = "NO"
a = 255
b = 0
c = 0
Case Is = "OK"
a = 0
b = 255
c = 0
End Select
Me.Label4.BackColor = RGB(a, b, c)


End Sub

 

[JackDanIce]

Untested, but try:

Private Sub UserForm_Initialize()

Dim wsPath  As Excel.Worksheet

Dim s       As String

Dim a       As Long
Dim b       As Long
Dim c       As Long
Dim x       As Long


    c = 0
    Set wsPath = ThisWorkbook.Sheets("Path")
    
    Application.ScreenUpdating = False
            
    With wsPath
    
        For x = 2 To 5
            
            s = .Range("B" & x).value
            
            Select Case s
                Case Is = "NO"
                    a = 255
                    b = 0
                Case Is = "OK"
                    a = 0
                    b = 255
                Case Else
                
            End Select
            
            With Me.Controls("Label" & x - 1)
                .Caption = CStr(wsPath.Range("A" & x).value)
                .BackColor = RGB(a, b, c)
            End With
            
        Next x
        
    End With
                
    Set wsPath = Nothing
    
    Application.ScreenUpdating = True

End Sub

 

[yasawa]

Works just fine

Thank You

 

[JackDanIce]

You're welcome.