Set background color on userform label from cell with conditional formatting inside certain range

[Jimmypop]

This has probably been asked before and I have tried numerous solutions however I do not know what I am doing wrong but here goes:

I have a worksheet in my workbook named “5S Worksheet”. On this worksheet columns “K” and “L” are merged to display a percentage value which is filled with a certain colour depending on value in cell. This is done with conditional formatting. Value can be from 0% to 100%.

I then created a button that when clicked will activate a userform called “DeleteRec”. When userform is activated it displays info to the user, e.g. record 1 has a value of 100%, 2 has a value of 50%, 3 has a value of 25% etc.

Next to record 1 I have added a label (label51) which shows the value of whatever percentage. I got this right so it can show the percentage in the label. (on userform)

My problem is I also want to set the background colour of the label to whatever is in the cell, so let’s say value is 100% and colour is green then label51 on userform must show 100% and be coloured green. I get this to work using select case.

However, it no longer works when I extend select case to cover the subsequent cells beneath. My whole range is from K12 to K112. So when I update

Select Case Sheets("5S Worksheet").Range("K12").value

to

Select Case Sheets("5S Worksheet").Range("K12:K112").value

I get a type 13 mismatch.

Here is the code so far: hope someone can assist.



PS. I know userform (screenshot attached) looks like a dogs breakfast but I am still designing it lol ?….

Private Sub UserForm_Initialize()
    Dim vCol
    Select Case Sheets("5S Worksheet").Range("K12").value
    Case Is = Empty
        vCol = vbWhite
    Case 0 To 0.24
        vCol = RGB(255, 0, 0)
    Case 0.25 To 0.49
        vCol = RGB(255, 255, 0)
    Case 0.5 To 0.99
        vCol = RGB(255, 192, 0)
    Case Is = 1
        vCol = RGB(0, 176, 80)
    Case Else
        vCol = vbWhite
    End Select
    
    With Sheets("5S Worksheet").Range("K12")
        Label51.Caption = .Text
        Label51.BackColor = vCol
    End With
    With Sheets("5S Worksheet").Range("K14")
        Label52.Caption = .Text
        Label52.BackColor = vCol
    End With
    With Sheets("5S Worksheet").Range("K16")
        Label53.Caption = .Text
        Label53.BackColor = vCol
    End With
    With Sheets("5S Worksheet").Range("K18")
        Label54.Caption = .Text
        Label54.BackColor = vCol
    End With

 

[Fluff]

There's no need for the select case, you can do it like

Private Sub UserForm_Initialize()
   With Sheets("5S Worksheet")
      With .Range("K12")
         Label51.Caption = .Text
         Label51.BackColor = .DisplayFormat.Interior.Color
      End With
      With .Range("K14")
         Label52.Caption = .Text
         Label52.BackColor = .DisplayFormat.Interior.Color
      End With
      With .Range("K16")
         Label53.Caption = .Text
         Label53.BackColor = .DisplayFormat.Interior.Color
      End With
      With .Range("K18")
         Label54.Caption = .Text
         Label54.BackColor = .DisplayFormat.Interior.Color
      End With
   End With
End Sub

 

[Jimmypop]

There's no need for the select case, you can do it like

Private Sub UserForm_Initialize()
   With Sheets("5S Worksheet")
      With .Range("K12")
         Label51.Caption = .Text
         Label51.BackColor = .DisplayFormat.Interior.Color
      End With
      With .Range("K14")
         Label52.Caption = .Text
         Label52.BackColor = .DisplayFormat.Interior.Color
      End With
      With .Range("K16")
         Label53.Caption = .Text
         Label53.BackColor = .DisplayFormat.Interior.Color
      End With
      With .Range("K18")
         Label54.Caption = .Text
         Label54.BackColor = .DisplayFormat.Interior.Color
      End With
   End With
End Sub

And I almost dropped an F-Bomb in the office. Thank you very much seeing as this has been with me for last two days.??

 

[Fluff]

You're welcome & thanks for the feedback