Unless you are using Excel 2002 or late in which case you can. This
examle changes the tab color every time the value in Cell A1 changes.
This is worksheet event code. Right click the sheet tab, select view
code and paste the event in there. Change it to suit your needs:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
If Target.Address = "$A$1" Then
Select Case Target.Value
Case 5
Me.Tab.ColorIndex = 36
Case 6
Me.Tab.ColorIndex = 35
Case Else
Me.Tab.ColorIndex = xlNone
End Select
End If
End If
End Sub

Hope this helps
Rowan

Leith Ross wrote:
Hello SCAScot,

You can't change the color of the Worksheet tabs. The Tabs object class
doesn't have a BackColor property like most objects, and the other
obstacle is Excel doesn't expose the Worksheet Tab properties through
VBA.

Sincerely,
Leith Ross