Excel VBA tab colour automatically changes - controlled by if statement

[justlearning1]

I am completely new to VBA excel. I would like to change the tab colour in a workbook based on the results of an if statement in A1

Here is what I currently have based on code I got from this site. I have no idea what to change in this code so that it responds to an if statement rather than something that was typed in the cell. Thanks!


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Not Intersect(Target, Sh.Range("G44")) Is Nothing Then Select Case UCase(Target.Value) Case Is = "LOW" Sh.Tab.ColorIndex = 10 Case Is = "MED" Sh.Tab.ColorIndex = 6 Case Is = "HIGH" Sh.Tab.ColorIndex = 3 Case Else Sh.Tab.ColorIndex = -4142 End Select End If End Sub</pre>

 

[VoG]

Try

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Select Case UCase(Sh.Range("G44").Value)
    Case "LOW": Sh.Tab.ColorIndex = 10
    Case "MED": Sh.Tab.ColorIndex = 6
    Case "HIGH": Sh.Tab.ColorIndex = 3
    Case Else: Sh.Tab.ColorIndex = -4142
End Select
End Sub

 

[justlearning1]

Thanks! Worked like a charm!