VBA to Automatically Change Tab Colors Based on Worksheet Data

[cvincent]

I have tried to find how to set vba code to check each tab in a workbook when the file is opened each day, and based on some date values and other criteria, change the tab color accordingly. My macro is below, but it isn't working. It is changing some tabs blue, and some red, regardless of the date values. This file is loaded with various macros, one of which allows the user to add a new worksheet. Sometimes a user could have as many as 60 worksheet tabs open at one time. When they put a worksheet on "HOLD" status, I want it to turn blue. Once the file has been on Hold, and the Hold expires, I want it to turn red - regardless of how many tabs they have going. The verbiage "Hold Expired" comes from an "IF" statement that checks the date it is set to expire; i.e. if "Today()" is less than the end Hold date, then enter "Hold Expired", otherwise enter "". I have separate macros to run each tab color. Can someone advise what I am doing wrong? Your expertise would be very much appreciated

Private Sub Worksheet_Calculate()
Static oldval

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets


If Range("B72").Value = "HOLD EXPIRED" And Range("C54").Value = "HOLD" Then
'
Call TabColorRed
'
End If

If Range("B72").Value <> "HOLD EXPIRED" And Range("O54").Value = "HOLD" Then
'
Call TabColorBlue
'
End If

Next ws

End Sub

 

[wideboydixon]

Excel 2010 and later:

ws.Tab.Color = RGB(255, 0, 0) ' Red
ws.Tab.Color = RGB(0, 0, 255) ' Blue

WBD

 

[cvincent]

Thank you wideboydixon. However I need the macro to check the data in the specified worksheet cells, and then based on that data, change the color accordingly.

 

[wideboydixon]

I was just re-using the ws variable that you had in your code.

 

[cvincent]

I will definitely use that wideboydixon. Thank you!

Are you also able to advise about the macro I am trying to build? I want it to run automatically when the file is opened - to check certain cells in each visible tab and change the tab color based on the criteria in those cells.