automatically change tab color

R

RSXchin

Well-known Member

Joined

Oct 23, 2010

Messages

758

• Jul 15, 2011

• #2

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

 

Upvote 0

R

RSXchin

Well-known Member

Joined

Oct 23, 2010

Messages

758

• Jul 15, 2011

• #3

put this into the "ThisWorkbook" section

Code:

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

 

Upvote 0

S

Sammi8796

Board Regular

Joined

Dec 12, 2007

Messages

96

• Jul 15, 2011

• #4

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.

 

Upvote 0

R

RSXchin

Well-known Member

Joined

Oct 23, 2010

Messages

758

• Jul 15, 2011

• #5

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.

 

Upvote 0

S

Sammi8796

Board Regular

Joined

Dec 12, 2007

Messages

96

• Jul 15, 2011

• #6

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.

 

Upvote 0

R

RSXchin

Well-known Member

Joined

Oct 23, 2010

Messages

758

• Jul 15, 2011

• #7

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

 

Upvote 0

S

Sammi8796

Board Regular

Joined

Dec 12, 2007

Messages

96

• Jul 15, 2011

• #8

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.

 

Upvote 0

R

RSXchin

Well-known Member

Joined

Oct 23, 2010

Messages

758

• Jul 15, 2011

• #9

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

 

Upvote 0