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

B

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.
 
G

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
 
B

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
 
B

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
 
B

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.
 
B

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top