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

  • Thread starter Thread starter BRC
  • Start date Start date
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.
 
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
 
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
 
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
 
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.
 
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.
 
Back
Top