How can I change sheet tab color based on cell value in sheet?

I use an Excel spreadsheet form to track various landscaping projects. I
denote the status of the project by using colors assigned by conditional
formatting based on the value of a status drop-down listbox on the sheet,
which works great, but I'd also like the tab color of the sheet to match the
status the various status colors I've chosen. Is there a way to do this?

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

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
>
>