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" <[email protected]> wrote in message news:0N20f.58321$8q.21432@lakeread01...
> 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-

[MaggieMagill]

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in
news:#[email protected]:

> 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
>
> "MaggieMagill" <[email protected]> wrote in message
> news:0N20f.58321$8q.21432@lakeread01...
>> 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-
>
>
>
>

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

"MaggieMagill" <[email protected]> wrote in >
> 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.