Wolfster63
New Member
- Joined
- May 2, 2018
- Messages
- 24
Back in early May, with the help of Mumps and My Answser is this, I was able to create a status board used by our operating room. Because Conditional Formating was not an option, we used VBA that changed the colors of a group of cells based on a drop down box value.
The following code works great:
What would be great would be if the cells would flash for about 20 seconds before ending up with the final color. This could alert folks as to a patient getting ready to come out of the OR.
I've tried a few solutions unsucessfully.
Any ideas?
Will
The following code works great:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 5/2/18 6:15 PM EDT
If Intersect(Target, Range("A3:A62,L3:L63")) Is Nothing Then Exit Sub
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim r As Long
Dim c As Long
r = Target.Row
c = Target.Column
Select Case Target.Value
Case "Turn Over"
Cells(r, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 0, 51)
Cells(r + 1, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 0, 51)
Case "Closing"
Cells(r, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 153, 0)
Cells(r + 1, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 153, 0)
Case "In OR"
Cells(r, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 255, 0)
Cells(r + 1, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 255, 0)
Case "Ready"
Cells(r, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 255, 255)
Cells(r + 1, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 255, 255)
Case "Done"
Cells(r, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 255, 255): Cells(r, c).Offset(, 1).Resize(, 9).Font.Color = vbWhite
Cells(r + 1, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 255, 255): Cells(r + 1, c).Offset(, 1).Resize(, 9).Font.Color = vbWhite
Case "Cancelled"
Cells(r, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 255, 255): Cells(r, c).Offset(, 1).Resize(, 9).Font.Color = vbRed: Cells(r, c).Offset(, 1).Resize(, 9).Font.Strikethrough = True
Cells(r + 1, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 255, 255): Cells(r + 1, c).Offset(, 1).Resize(, 9).Font.Color = vbRed: Cells(r + 1, c).Offset(, 1).Resize(, 9).Font.Strikethrough = True
Case "Reset"
Cells(r, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 255, 255): Cells(r, c).Offset(, 1).Resize(, 9).Font.Color = vbBlack: Cells(r, c).Offset(, 1).Resize(, 9).Font.Strikethrough = False: Cells(r, c).Offset(, 1).Resize(, 9).Font.FontStyle = "Bold"
Cells(r + 1, c).Offset(, 1).Resize(, 9).Interior.Color = RGB(255, 255, 255): Cells(r + 1, c).Offset(, 1).Resize(, 9).Font.Color = vbBlack: Cells(r + 1, c).Offset(, 1).Resize(, 9).Font.Strikethrough = False: Cells(r + 1, c).Offset(, 1).Resize(, 9).Font.FontStyle = "Bold"
End Select
End Sub
What would be great would be if the cells would flash for about 20 seconds before ending up with the final color. This could alert folks as to a patient getting ready to come out of the OR.
I've tried a few solutions unsucessfully.
Any ideas?
Will