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]

Give several examples, don't limit yourself to just one example

When I said that I don't understand you, it's because I simply don't understand you and that's why I requested several examples and you only put this: (28-Oct), it's not even a good example.

Without examples I'm just going to try to guess what you need and I'm going to put one code after another, without reaching a solution.


If on the same sheet you have the following dates:

So what color do you want the tab?​

-------------------------

You must put all the possible combinations you have on a single sheet in an example and tell me what color you want the tab.​

​

Like I said, don't limit yourself to just one example.​

It is not my requirement and I gave 3 examples. So I hope that you will help me to help you.​

----- --
I hope to hear from you soon.
Respectfully
Dante Amor
----- --

 

[seiexcel]

Dante thank you for your patience with me in this process, I greatly appreciate it.
Below is an example from columns A, B & C in one of the sheets in the workbook.
I have conditionally formatted any cell in column A to be red when it is the current date, in this example, that occurs in 5 days. That works.
After I complete the order, I will highlight the row yellow, any row with today's date, manually.

My ask is for code, if possible,
for any sheet tab in the workbook to be red when any cell in column A of that sheet has a value equal to the current date. (my original question)
for any sheet tab to be blue if that date is up to 5 days previous to the current date, only if I haven't manually highlighted that cell/row.
otherwise the default color remains for the sheet tabs in the workbook

Thank you in advance for you expertise and willingness to help out.

ORDER	LEAD	Electrical
DATE	TIME	Manufacturer
28-Oct	3	HOFFMAN
28-Oct	3	HOFFMAN
28-Oct	3	HOFFMAN
28-Oct	3	HOFFMAN

 

[DanteAmor]

I still don't understand and you didn't give many examples, in truth, I need you to give several examples.
And you didn't answer my question. If I have several dates, what color do I use?

In fact, in your example you did not put a date equal to today.

I hope for more examples and in each example the color you want in the tab.

 

[seiexcel]

I hope this is helpful, thank you.

 

[Skyybot]

@seiexcel , I've been following this thread, and I think what you're looking for is impossible because it seems (by the examples given) that it's possible to have both red and blue cells in one sheet. Sheet tabs can only be one color at a time (technically you could have a colored pattern I guess), but what would be the expected outcome if that happened? It think that if you are using the Workbook Open event that it would be easier just to copy the requested data to new Sheets named "Current" and "Overdue". Would that work for you?

 

[DanteAmor]

I hope this is helpful, thank you.

Try the following code:

Private Sub Workbook_Open()
  Dim sh As Worksheet
  Dim a() As Variant
  Dim i As Long
  Dim bRed As Boolean, bYellow As Boolean
  
  For Each sh In Sheets
    sh.Tab.ColorIndex = xlNone
    bYellow = False
    bRed = False
    
    Erase a()
    a = sh.Range("A1:A" & sh.Range("A" & Rows.count).End(xlUp).Row).Value
    
    For i = 1 To UBound(a, 1)
      If a(i, 1) <> "" And IsDate(a(i, 1)) Then
        If a(i, 1) >= Date - 5 And a(i, 1) < Date Then
          If sh.Range("A" & i).Interior.Color = vbYellow Then
            bYellow = True
            sh.Tab.Color = vbYellow
            Exit For
          End If
        End If
      End If
    Next

    If bYellow = False Then
      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
            bRed = True
            Exit For
          End If
        End If
      Next
      
      If bRed = False Then
        For i = 1 To UBound(a)
          If a(i, 1) <> "" And IsDate(a(i, 1)) Then
            If a(i, 1) >= Date - 5 And a(i, 1) < Date Then
              sh.Tab.Color = vbBlue
              Exit For
            End If
          End If
        Next
      End If
    End If
  Next
End Sub