I am trying to automate somre tracking on a multiple tab worksheet. so that
when a value in cell X is = X it changes the tab to green to show its done.

Sub Tab_color_change()
'
' Tab_color_change Macro
' Macro recorded 8/12/2008 by BRC
'

'
Sheets("Test log ").Select
If t39 = 10 Then
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 4
Else
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 3
End If
End Sub

it runs no errors but it doesn't matter whats in cell T39 it turns the tab
red.

try this

Sub test()
With Sheets("Test log")
If .Range("t39") = 10 Then
.Tab.ColorIndex = 4
Else
.Tab.ColorIndex = 3
End If
End With
End Sub



--


Gary


"BRC" wrote in message
...
I am trying to automate somre tracking on a multiple tab worksheet. so that
when a value in cell X is = X it changes the tab to green to show its done.

Sub Tab_color_change()
'
' Tab_color_change Macro
' Macro recorded 8/12/2008 by BRC
'

'
Sheets("Test log ").Select
If t39 = 10 Then
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 4
Else
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 3
End If
End Sub

it runs no errors but it doesn't matter whats in cell T39 it turns the tab
red.

I didn't even know you could change the tab color! But I'm pretty sure I see
what the problem is: Unless there's more to the code than this, you meant
t39 to indicate a cell, but VBA doesn't know that; it's clear to VBA that t39
is an undeclared variable name, which is initialized to Empty, which
evaluates as 0. Zero is never = 10, so this program always takes the second
fork. What you need is to change the If statement so VBA knows you're
referring to a cell with that address, something like this:

If ActiveWorkbooks.Sheets("Test log ").Range("T39") = 10 Then
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 4
Else
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 3
End If

....or, to make it a little simpler:

With ActiveWorkbooks.Sheets("Test log ")
If .Range("T39") = 10 Then
.Tab.Colorindex = 4
Else
.Tab.ColorIndex = 3
End If
End With

--- "BRC" wrote:
I am trying to automate somre tracking on a multiple tab worksheet. so that
when a value in cell X is = X it changes the tab to green to show its done.

Sub Tab_color_change()
Sheets("Test log ").Select
If t39 = 10 Then
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 4
Else
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 3
End If
End Sub

it runs no errors but it doesn't matter whats in cell T39 it turns the tab
red.

Thanks that did the trick :) only 10 tabs to write the code for I looked ar
..range but didn't quite get it thought it wanted a range of cells. Thank you
very much

"Gary Keramidas" wrote:

try this

Sub test()
With Sheets("Test log")
If .Range("t39") = 10 Then
.Tab.ColorIndex = 4
Else
.Tab.ColorIndex = 3
End If
End With
End Sub



--


Gary


"BRC" wrote in message
...
I am trying to automate somre tracking on a multiple tab worksheet. so that
when a value in cell X is = X it changes the tab to green to show its done.

Sub Tab_color_change()
'
' Tab_color_change Macro
' Macro recorded 8/12/2008 by BRC
'

'
Sheets("Test log ").Select
If t39 = 10 Then
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 4
Else
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 3
End If
End Sub

it runs no errors but it doesn't matter whats in cell T39 it turns the tab
red.

Bob thank you. I'm sure between you and Gary Ill be able to work things out
and make things a little smoother. :) will probably even create a button to
run the macro when I update the fields. Again thanks for the help.

"Bob Bridges" wrote:

I didn't even know you could change the tab color! But I'm pretty sure I see
what the problem is: Unless there's more to the code than this, you meant
t39 to indicate a cell, but VBA doesn't know that; it's clear to VBA that t39
is an undeclared variable name, which is initialized to Empty, which
evaluates as 0. Zero is never = 10, so this program always takes the second
fork. What you need is to change the If statement so VBA knows you're
referring to a cell with that address, something like this:

If ActiveWorkbooks.Sheets("Test log ").Range("T39") = 10 Then
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 4
Else
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 3
End If

...or, to make it a little simpler:

With ActiveWorkbooks.Sheets("Test log ")
If .Range("T39") = 10 Then
.Tab.Colorindex = 4
Else
.Tab.ColorIndex = 3
End If
End With

--- "BRC" wrote:
I am trying to automate somre tracking on a multiple tab worksheet. so that
when a value in cell X is = X it changes the tab to green to show its done.

Sub Tab_color_change()
Sheets("Test log ").Select
If t39 = 10 Then
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 4
Else
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 3
End If
End Sub

it runs no errors but it doesn't matter whats in cell T39 it turns the tab
red.

You may want to look at Worksheet_Change events to have it change the tab
color in real time instead of having to run another macro.
--
HTH,
Barb Reinhardt



"BRC" wrote:

I am trying to automate somre tracking on a multiple tab worksheet. so that
when a value in cell X is = X it changes the tab to green to show its done.

Sub Tab_color_change()
'
' Tab_color_change Macro
' Macro recorded 8/12/2008 by BRC
'

'
Sheets("Test log ").Select
If t39 = 10 Then
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 4
Else
ActiveWorkbook.Sheets("Test log ").Tab.ColorIndex = 3
End If
End Sub

it runs no errors but it doesn't matter whats in cell T39 it turns the tab
red.