Automatically change tab color as it relates to cell value

[seiexcel]

I have a workbook with multiple sheets, each sheet is formatted identically, the only difference is the number of rows in each sheet.

I would like to have the tab color change to red automatically when any cell in column A is the current date without there being additional activity in the sheet itself.
As it stands, I have the date entered in the cells in column A as dd-month.

Thanks in advance for any help.

 

[DanteAmor]

Hi and welcome to MrExcel.

without there being additional activity in the sheet itself

For something to run automatically, it needs to happen, something we call an "event."
In this case, if you do not want automation to occur when modifying something in the sheet, we can use one of the following:
1) When you open the book.
2) When you activate the sheet.
There are more events, but I think we can start with those 2.
Which one would you like to use?

 

[seiexcel]

Thank you for the quick response and interest in helping out.
The book will be opened daily, so let's start there.

 

[DanteAmor]

Ok, when you open the workbook, the following code will automatically be executed, check all the sheets and all the values in column A of each sheet, if it finds a date equal to today, then change the color of the tab to red .

Put the following code in ThisWorkbook events:

Private Sub Workbook_Open()
  Dim sh As Worksheet
  Dim a As Variant
  Dim i As Long
  
  For Each sh In Sheets
    a = sh.Range("A1", sh.Range("A" & Rows.Count).End(3))
    For i = 1 To UBound(a)
      If a(i, 1) = Date Then
        sh.Tab.Color = vbRed
        Exit For
      End If
    Next
  Next
End Sub

ThisWorkbook EVENT
- Open the VB Editor (press Alt + F11).
- Over in the Project Explorer, double click on ThisWorkbook.
- In the white panel that then appears, paste the above code.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[seiexcel]

Dante, this worked precisely as I'd hoped for, thank you very much.
What would the code look like if I wanted to add an "Overdue" notice? For instance, if the date in column A is up to five (5) days overdue, the tab would be blue?

Adam

 

[DanteAmor]

Try this:

Private Sub Workbook_Open()
  Dim sh As Worksheet
  Dim a As Variant
  Dim i As Long
  
  For Each sh In Sheets
    a = sh.Range("A1", sh.Range("A" & Rows.count).End(3))
    For i = 1 To UBound(a)
      If a(i, 1) <> "" And IsDate(a(i, 1)) Then
        If a(i, 1) = Date Then
          sh.Tab.Color = vbRed
          Exit For
        ElseIf a(i, 1) <= Date - 5 Then
          sh.Tab.Color = vbBlue
          Exit For
        End If
      End If
    Next
  Next
End Sub

 

[seiexcel]

Try this:

Private Sub Workbook_Open()
  Dim sh As Worksheet
  Dim a As Variant
  Dim i As Long
 
  For Each sh In Sheets
    a = sh.Range("A1", sh.Range("A" & Rows.count).End(3))
    For i = 1 To UBound(a)
      If a(i, 1) <> "" And IsDate(a(i, 1)) Then
        If a(i, 1) = Date Then
          sh.Tab.Color = vbRed
          Exit For
        ElseIf a(i, 1) <= Date - 5 Then
          sh.Tab.Color = vbBlue
          Exit For
        End If
      End If
    Next
  Next
End Sub

 

[seiexcel]

Thank you, Dante,
the new code has changed all the tabs colors to blue regardless of the date, with the exception of tabs where today's date is in a cell in column A, those are still red (as desired).
Additionally, is there a way to override the "overdue" blue color change if the cell in column A is highlighted yellow?

 

[DanteAmor]

the new code has changed all the tabs colors to blue regardless of the date, with the exception of tabs where today's date is in a cell in column A, those are still red (as desired).
Additionally, is there a way to override the "overdue" blue color change if the cell in column A is highlighted yellow?

I'm not understanding what you need.

You could give examples of what you have in column A, what you want to be verified. If you find what date, what color you want the tab.
Give several examples, don't limit yourself to just one example, since I'm not understanding.

 

[seiexcel]

Thanks Dante,
Column A contains cell values that are dates material needs ordered, for example, (28-Oct).
I have conditionally formatted those cells to highlight red when the date entered is today's date, I would like to have the tabs associated with each sheet turn red when those cells are equal to today's date and to be blue if it is past due by up to 5 days, except when I've highlighted those cells yellow, meaning those dates have been acknowledged and the material ordered.
Thus far, the tab color to red notification had worked, although this morning I have a "debug" error on the "For i = (i, 1) = Date Then" code.

Thank you.