Excel WorkSheet Tab to change the color of the tab to red if any cell in the "B" Column is past due, or Red.
I have a workbook full of tabs, but the report tab has a list of reports and the due dates on the reports. With conditional Formatting, the cell will turn red when due date is close, but Since I only visit the tab a few times a month, I want the tab to turn red when a task is coming up, so I remember to check it.
I have found this code on this site and modified it to what I thought would work:
Option ExplicitPrivate Sub Workbook_SheetDeactivate(ByVal Sh As Object)If (Sh.Range("B2:B9").Interior.Color = RGB(255, 0, 0)) Then Sh.Tab.Color = vbRedEnd Sub
And as you can probably see, the tab turned red, but will stay red even if nothing is past due.
I need the vba to check if any of the cells in range either 1: have a conditional formatting applied to the cell, or 2: test against the date in the cell. past due or within 5 days should turn the tab red.
So the original code was set for the whole workbook, I only want it to work with one tab called "Reports" and only 8 cells in column B. (B2:B9). Each of those cells has a data list(drop down list), so I can change the date once the task is done.
I tried "Function ActiveCondition" as well. Not sure what the correct function would be to have the event triggered after click? testTabColor.xlsm