Q: How to change tab color based on cell value

[John]

Using VBA and Excel 2002, how can I change a tab color whenever any
cell on that tab/sheet has its interior color becomes equal to index 4
(green)?

What I'm trying to accomplish:
Cells on a sheet contain conditional formatting that when given a
certain condition will change the interior color of that cell to
green. This workbook contains many sheets...so to make it quicker to
find those green cells, the related tab will also change to green.

Any ideas?

[Frank Kabel]

Hi
the bad news is that you can't invoke a macro directly triggered by the
color change of a conditional format. In addition you're not able to
read the colorindex of a conditional formated cell in VBA
(.interior.colorindex will always return the colorindex of the default
color whether the condition is met or not).

One workaround: You can use the worksheet_change event and check the
conditions programmatical. That is you reprogram the conditions of your
conditional format.
e.g.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value = 1 Then 'change this to your conditions
Application.EnableEvents = False
Activesheet.Tab.ColorIndex = 4
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

this test if a cell in the range A1:A100 is '1' and if yes changes the
tab color to green

--
Regards
Frank Kabel
Frankfurt, Germany