Tab color based on cell value

The Question

This week's question to Allen Wyatt @ ExcelTips had to do with dynamically changing worksheet tab colors.  One of Allen's subscribers said he knew how to mouse-click through changing worksheet tab colors but was wondering if this could be automated based on a cell value.

The Object(s)

At the object-level, the Tab object is a child of the Sheet object.  The Tab object has a Color attribute that can be set just like Font and Fill colors, e.g. ws.Tab.Color = rgbRed.

This  raises the question whether the Tab object, like the Range object might be used with the FormatCondition object.  It cannot.  The FormatCondition object applies only to the Range object. 

The Code

Just for sake of discussion, this is the code within a standalone Sub.  

Sub ChangeTabColor(ws as Worksheet)

    If ws.Range("A5").Value > 5 Then

        ws.Tab.Color = rgbRed

    Else

        ws.Tab.Color = rgbGreen

    End If

End Sub

The key points are

1. Scoping to the Worksheet object, in this case using the ws as Worksheet parameter
2. Accessing the Tab object of the Worksheet object
3. The test, If ws.Range("A5").Value > 5
4. The assignment of the color

The Event Procedure

There are three options for where to put this code.

Standalone Sheet Option

If the value is entered or is recalculated only when the sheet is active,  then it can go in the worksheet's Worksheet_Change event procedure.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Me.Range("A5").Value > 5 Then

        Me.Tab.Color = rgbRed

    Else

        Me.Tab.Color = rgbGreen

    End If

End Sub

Note that the Target parameter is unimportant.  The "Me" uses the worksheet as the 

Interdependent Sheets Option

If the value is the result of changes made in other worksheets, then there are two choices, either the Worksheet_Calculate or the Workbook_SheetChange event procedure will work.

Worksheet_Calculate

This is exactly the same code as for the Worksheet_Change event procedure.

Private Sub Worksheet_Calculate()

    If Me.Range("A5").Value > 5 Then

        Me.Tab.Color = rgbRed

    Else

        Me.Tab.Color = rgbGreen

    End If

End Sub

Workbook_SheetChange

When using the Workbook_SheetChange event procedure the reference to the worksheet is more verbose.

Private Sub Workbook_SheetChange(ByVal Target As Range)

    If Me.Worksheets("Sheet1").Range("A5").Value > 5 Then

        Me.Worksheets("Sheet1").Tab.Color = rgbRed

    Else

        Me.Worksheets("Sheet1").Tab.Color = rgbGreen

    End If

End Sub