Jimmypop
Well-known Member
- Joined
- Sep 12, 2013
- Messages
- 753
- Office Version
- 365
- Platform
- Windows
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
to
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 ?….
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
VBA Code:
Select Case Sheets("5S Worksheet").Range("K12").value
VBA Code:
Select Case Sheets("5S Worksheet").Range("K12:K112").value
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 ?….
VBA Code:
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