| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Tab Changes
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Tab Changes
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Tab Changes
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: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Tab Changes
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 -- Regards, Nigel "akemeny" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Tab Changes
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Tab Changes
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Tab Changes
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Tab Changes
The macro is case sensitive for the word closed.
Modify the line to this If UCase(Target.Value) = "CLOSED" Then Mike "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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Tab Changes
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
| Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
| how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
| macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
| Start Macro / Stop Macro / Restart Macro | Excel Programming | |||
Linear Mode
