Tab Color from cell value?

[MaggieMagill]

Is there a way to change the tab color of a worksheet if a certain cell has
one value or another?

"If cell value is more than zero then tab color is green" type of scenerio?

-Office Pro 2003-

 

[Bernie Deitrick]

Maggie,

Sure. Copy the code below, right click on the sheet of interest, select "View Code" and paste the
code in the window that appears. Change the "A3" to the address of the cell that you want to
monitor.

This assumes that the cell is calculated rather than entered manually. If you want to enter a value
into the cell manually, you will need to use the change event - post back if you can't figure it
out.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
If Range("A3").Value > 0 Then
Range("A3").Parent.Tab.ColorIndex = 4
Else
Range("A3").Parent.Tab.ColorIndex = 3
End If
End Sub

 

[MaggieMagill]

Subject: Re: Tab Color from cell value?
From: "Bernie Deitrick" <deitbe @ consumer dot org>
Newsgroups: microsoft.public.excel.worksheet.functions

Maggie,

Sure. Copy the code below, right click on the sheet of interest,
select "View Code" and paste the code in the window that appears.
Change the "A3" to the address of the cell that you want to monitor.

This assumes that the cell is calculated rather than entered manually.
If you want to enter a value into the cell manually, you will need to
use the change event - post back if you can't figure it out.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Calculate()
If Range("A3").Value > 0 Then
Range("A3").Parent.Tab.ColorIndex = 4
Else
Range("A3").Parent.Tab.ColorIndex = 3
End If
End Sub

Hot diggity! Now THAT is slick!!! I've never played in the world of VB
before (a little Pascal some 15 years ago) but after seeing what THAT can
do for me... Time to hit the VB Help files.

I added an "If" routine for less than zero, changed it so zero or no value
leaves the default tab color with "xlColorIndexNone" and found the color-
index value help file.

All in all - too cool! Thanks a ton!

 

[David McRitchie]

Incidentally this was the type of posting where the version of Excel
makes a difference and the poster identified her version.

The ability to color worksheet tabs was added in Excel 2002, so
it's not something that everybody can do, though there may be other
solutions for most requests for color worksheet tabs.
http://www.mvps.org/dmcritchie/excel/colors.htm#tabs