Macro for Tab Changes

[akemeny]

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

 

[Mike H]

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

 

[Stefi]

Maybe something like this (in Excel 2003):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C3" And Target.Value = "Closed" Then
ActiveSheet.Tab.ColorIndex = 5
End If
End Sub

Regards,
Stefi

„akemeny†ezt írta:

 

[Nigel]

Place code behind the sheet in question

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$R$3" Then
Select Case UCase(Trim(Target.Value))
Case Is = "CLOSED": ActiveSheet.Tab.Color = vbBlue
Case Is = "OPEN": ActiveSheet.Tab.Color = vbRed
' repeat lines for other test text - color option
End Select
End If
End Sub

 

[akemeny]

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]

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]

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]

The macro is case sensitive for the word closed.

Modify the line to this
If UCase(Target.Value) = "CLOSED" Then

Mike

 

[Gord Dibben]

Then you need worksheet_calculate event, not change event.


Gord Dibben MS Excel MVP