want to change tab color based on the info in a cell.

[BRC]

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.

 

[Gary Keramidas]

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

 

[Bob Bridges]

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]

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

 

[BRC]

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.

 

[Barb Reinhardt]

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.