Hello All,
I am working on a KPI document that requires a "stoplight" color to indicate the health of each vessel in an oil system based on the score it is given ( 0=white,3=red,2=yellow, Everything else=green). I am attempting to use the code below, and it has worked on ONE vessel in another document, however I do not know how to repeat the vba instructions properly to make it test each cell/vessel. Also I made a test document to show you what I'm working with and it will now not work for this document. I have renamed all of the shapes to their vessel names. Please see attached spreadsheet for the test. ExcelForumsTest.xlsm Any help on this would really help me out, I am new to VBA but trying to learn as quickly as possible. Thank you!
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b2")) Is Nothing Then
If IsNumeric(Target.Value) Then
If Target.Value = 0 Then
ActiveSheet.Shapes("Hp#2Separator").Fill.ForeColor.RGB = vbWhite
ElseIf Target.Value = 3 Then
ActiveSheet.Shapes("Hp#2Separator").Fill.ForeColor.RGB = vbRed
ElseIf Target.Value = 2 Then
ActiveSheet.Shapes("Hp#2Separator").Fill.ForeColor.RGB = vbYellow
Else
ActiveSheet.Shapes("Hp#2Separator").Fill.ForeColor.RGB = vbGreen
End If
End If
End If
End Sub
Bookmarks