Change Tab Color Based on Cell Value

[ScottA333]

I have been trying to use a few Macros that I've found online to change each tab color on my workbook referencing cell E1 on each tab with no luck. I would like the tab to be red if E1 does not equal 0, green if it does equal 0.

Any help would be greatly appreciated!

 

[CesarF]

You don't need any macro to do this, just use conditional formatting instead

 

[John_w]

If you want the tab colour to change dynamically when cell E1 on the sheet is changed , put this code in the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Range("E1").Value = 0 Then
        Sh.Tab.Color = vbGreen
    Else
        Sh.Tab.Color = vbRed
    End If
End Sub

On the other hand, if you only want to change all the tabs when you run a macro, put this code in a standard module:

Public Sub Change_Tabs_Colour()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Range("E1").Value = 0 Then
            ws.Tab.Color = vbGreen
        Else
            ws.Tab.Color = vbRed
        End If
    Next
End Sub

 

[Fluff]

@CesarF Can you please explain how conditional formatting is meant to change the colour of the tab? That link is to change the colour of cells.

 

[CesarF]

@CesarF Can you please explain how conditional formatting is meant to change the colour of the tab? That link is to change the colour of cells.

Oooooops! my mistake... I skipped the "tab" word.

 

[ScottA333]

If you want the tab colour to change dynamically when cell E1 on the sheet is changed , put this code in the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Range("E1").Value = 0 Then
        Sh.Tab.Color = vbGreen
    Else
        Sh.Tab.Color = vbRed
    End If
End Sub

On the other hand, if you only want to change all the tabs when you run a macro, put this code in a standard module:

Public Sub Change_Tabs_Colour()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Range("E1").Value = 0 Then
            ws.Tab.Color = vbGreen
        Else
            ws.Tab.Color = vbRed
        End If
    Next
End Sub

@John_w

When I enter the code into the ThisWorkbook module then hit play it requests that I name the macro so I named it SheetChange but nothing is happening

First, should it be asking for a name? I have used other macros that have worked and they do not ask for a name. If it shouldn't be asking for a name, are you able to tell what I'm doing wrong?

Thank you!

 

[John_w]

You don't run or 'play' the macro; it runs automatically when cell E1 on any sheet is changed.

You've put the code in the correct place.

Put this in the ThisWorkbook module too, so that the tab's colour is changed when you click the tab and you should see the effect of the code.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Range("E1").Value = 0 Then
        Sh.Tab.Color = vbGreen
    Else
        Sh.Tab.Color = vbRed
    End If
End Sub

 

[ScottA333]

You don't run or 'play' the macro; it runs automatically when cell E1 on any sheet is changed.

You've put the code in the correct place.

Put this in the ThisWorkbook module too, so that the tab's colour is changed when you click the tab and you should see the effect of the code.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Range("E1").Value = 0 Then
        Sh.Tab.Color = vbGreen
    Else
        Sh.Tab.Color = vbRed
    End If
End Sub

Thank you @John_w !