automatically change tab color

[Sammi8796]

Can anyone tell me if it's possible to change the tab color based on a formula in a cell on each tab? I want to change the tab color to red if the number in A1 is a negative number. If it's a positive number I just want it to remain the same, the default color. I have many tabs in the workbook and I'm using excell 2003. Thanks in advance.

 

[RSXchin]

You'll have to use VBA for this. Are you okay with that?

 

[RSXchin]

put this into the "ThisWorkbook" section

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    With Application
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook
        If sh.Range("a1") < 0 Then
            sh.Tab.ColorIndex = 30
        Else
            sh.Tab.ColorIndex = -4142
        End If
    Next
    With Application
        .Calculation = xlCalculationManual
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[Sammi8796]

I'm so bad at VBA. I have a really hard time trying to figure out how to make it work. Can you give me a step by step or is that asking too much? Any more specific help you can give me would be appreciated so much.

 

[RSXchin]

hit Alt+F11
on the left side, double click on "ThisWorkbook"
Paste that code in there

You're done. Everytime you enter something into a cell, it will do a check on each sheet in your workbook. If it's negative, the sheet will be Red. if it isn't, it'll turn it back to Grey.

 

[Sammi8796]

when i hit alt+f11 a white vba box come up with two drop downs at the top. One on the left says "general" and the other on the right says "declarations." is this where Im supposed to paste it? If so, when I do, do i hit save or just close the window? If this is correct, I did it and nothing happens. Can you help me along? I'm sorry I'm just so lost with this stuff. I've never worked with macros or vba.

 

[RSXchin]

on the left side (in the project explorer bar) click on "ThisWorkbook"

 

[Sammi8796]

Ok, i did that. Do I just close the window or is there something I'm supposed to click because it didn't work. I hit save and closed the vba window and I didn't see any tab turn color.

 

[RSXchin]

you have to change something. anything.
ALSO, dont forget to enable macros.