Then you need worksheet_calculate event, not change event.


Gord Dibben MS Excel MVP

On Thu, 18 Sep 2008 06:20:02 -0700, akemeny
wrote:

It's not working. Everything else still works exactly the same (which is a
good thing), but it still won't color the tab. Does the Macro you gave me
work when the cell referenced has a formula in it? I have the cell set to
display Open or Closed based on the formula in the cell.

"Mike H" wrote:

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then GoTo getmeout
If Target.Address = "$R$3" Then
If Target.Value = "Closed" Then
Application.EnableEvents = False
ActiveSheet.Tab.ColorIndex = 41
Application.EnableEvents = True
End If
End If
getmeout:
With Worksheets("June 13 - 2045875")
Application.EnableEvents = False
Call Sheet10.colortotalrow
Application.EnableEvents = True
End With
End Sub


Mike

"akemeny" wrote:

Mike,

I already have a Private Sub Worksheet_Change Macro set to automatically run
all of my macros. How would I use the macro you suggested below with the
following:

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("June 13 - 2045875")
Application.EnableEvents = False
Call Sheet10.colortotalrow
Application.EnableEvents = True
End With
End Sub

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$R$3" Then
If Target.Value = "Closed" Then
Application.EnableEvents = False
ActiveSheet.Tab.ColorIndex = 41
Application.EnableEvents = True
End If
End If
End Sub

Mike

"akemeny" wrote:

What (if any) Macro can I use to change the color of the Tab when a cell
contains a specific word.

For example:

When Cell R3 = Closed the Tab changes to blue