Auto change tab color of sheets based on cell value

Smoofinator

New Member
Joined
Feb 19, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I need to automatically change the tab color to red if cell L3 contains "TRUE." I need this to run automatically on each sheet upon opening the file. I have used the code below, but I've had to add it to each sheet. Unfortunately, this doesn't run automatically and won't work unless I actually click into the cell and press enter. Plus, I have to attach this code to every freaking sheet (there are over 100) in the workbook! In summation, I need to open the file, auto run a check on each sheet for the value in L3, and if that value is TRUE, I need the tab color of that sheet to change to red. Any other values besides TRUE, the tab color doesn't change (no color).

Note: The TRUE and FALSE values in L3 are generated based on whether another cell's value is <0 or >0 (in other words, L3 isn't a simple text cell, but contains an "=if..." formula). Not sure if this matters, but wanted to mention.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    MyVal = Range("L3").Text


    With ActiveSheet.Tab
        Select Case MyVal
            Case "TRUE"
                .Color = vbRed
            Case "FALSE"
                .ColorIndex = xlColorIndexNone
            Case Else
                .ColorIndex = xlColorIndexNone
        End Select
    End With
End Sub
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,512
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Try using the Workbook_Open event, which runs upon the opening of the workbook, and loop through all the sheets, and run the code on each one that way.
There are plenty of posts here (and on Google) that show you how to loop through sheets.
Post back here if you run into any issues.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,777
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top