VBA Changing tab color

I am trying to write a code in VBA to change the tab color based on cell value. The code I have now works when I manually input the key word. However, the cell is auto-populated by a VLOOKUP and when it auto-populates the tab does not change color. I need advice on how to change the VBA code to accommodate the VLOOKUP 

 

 

Current Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = “$A$6” Then

        Select Case Target.Value

        Case “Active”

            Me.Tab.Color = vbGreen

        Case “Hold”

            Me.Tab.Color = vbYellow

        Case “Closed”

            Me.Tab.Color = vbBlue

        End Select

    End If

End Sub

By: Hannah

Leave a Reply

Your email address will not be published. Required fields are marked *