Excel VBA: automatically adjust tab colour

Question

I have a cell ("L2") that is controlled with an if function to determine either "PASS", "ERROR" or "CHECK BOX" based on user input in other fields.

I want the colour of that respective tab to change automatically based on cell content whenever the value of cell L2 changes.

My code currently looks like this, but unfortunately, it doesn't work. Can you please have a look and help me out? Many thanks for your help!

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(False, False) = "L2" Then

    Select Case Target.Value
        Case "ERROR"
            Me.Tab.ColorIndex = 3
        Case "PASS"
            Me.Tab.ColorIndex = 4
        Case Else
            Me.Tab.ColorIndex = xlColorIndexNone
    End Select

End If
End Sub

EDIT:

I noticed something interesting: As the displayed value of cell "L2" is controlled via an if function that is contained within it, the vba does not appear to read the displayed value, but rather the if-statement from the cell. How can I get around this?

Thanks for your help.

Answer 1

In case you want the tab color to change depending on the value in the L2 then use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Range("$L$2").Value
        Case "ERROR"
            Me.Tab.ColorIndex = 3
        Case "PASS"
            Me.Tab.ColorIndex = 4
        Case Else
            Me.Tab.ColorIndex = xlColorIndexNone
    End Select
End Sub