Wolfster63
New Member
- Joined
- May 2, 2018
- Messages
- 24
I am trying to modify a status board for our hospital operating room that will enable whoever is updating the board to change the color of cells according to a value selected from a drop down box.
Unfortunately, I can't use conditional formatting hence the VBA route. I have be experimenting with some code as follows:
Sub ColorMeElmo()
Dim i As Long, r1 As Range, r2 As Range
For i = 2 To 15
Set r1 = Range("D" & i)
Set r2 = Range("A" & i & ":C" & i)
If r1.Value = "Turn Over" Then r2.Interior.Color = RGB(255, 0, 51)
If r1.Value = "Closing" Then r2.Interior.Color = RGB(255, 153, 0)
If r1.Value = "In OR" Then r2.Interior.Color = RGB(255, 255, 0)
If r1.Value = "Ready" Then r2.Interior.Color = RGB(255, 255, 255)
If r1.Value = "Done" Then r2.Interior.Color = RGB(255, 255, 255)
If r1.Value = "Done" Then r2.Font.Color = RGB(255, 255, 255)
Next i
End Sub
I need the code to execute each time a different value is selected from the drop down list. Right now, it only executes when the sheet is loaded.
I am not that proficient with VBA. I am hoping this could be a really simple fix.
Thanks in advance,
Will
Unfortunately, I can't use conditional formatting hence the VBA route. I have be experimenting with some code as follows:
Sub ColorMeElmo()
Dim i As Long, r1 As Range, r2 As Range
For i = 2 To 15
Set r1 = Range("D" & i)
Set r2 = Range("A" & i & ":C" & i)
If r1.Value = "Turn Over" Then r2.Interior.Color = RGB(255, 0, 51)
If r1.Value = "Closing" Then r2.Interior.Color = RGB(255, 153, 0)
If r1.Value = "In OR" Then r2.Interior.Color = RGB(255, 255, 0)
If r1.Value = "Ready" Then r2.Interior.Color = RGB(255, 255, 255)
If r1.Value = "Done" Then r2.Interior.Color = RGB(255, 255, 255)
If r1.Value = "Done" Then r2.Font.Color = RGB(255, 255, 255)
Next i
End Sub
I need the code to execute each time a different value is selected from the drop down list. Right now, it only executes when the sheet is loaded.
I am not that proficient with VBA. I am hoping this could be a really simple fix.
Thanks in advance,
Will