Excel WorkSheet Tab to change the color of the tab if any cell in the "B" Column is past due, or Red

Jim Schwetz
Jim Schwetz used Ask the Experts™
on
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 Explicit

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If (Sh.Range("B2:B9").Interior.Color = RGB(255, 0, 0)) Then Sh.Tab.Color = vbRed
End Sub

Open in new window

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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
I am guessing you need an Else statement as well:

Option Explicit

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If (Sh.Range("B2:B9").Interior.Color = RGB(255, 0, 0)) Then
Sh.Tab.Color = vbRed
Else
Sh.Tab.Color = vbWhite
End If
End Sub

Open in new window

SteveCost Accountant
Top Expert 2012

Commented:
I would pop something like this into the workbook before save event:

So in the 'ThisWorkbook' module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

For Each sh In ThisWorkbook.Sheets
    
    f = False
    
    For Each c In sh.Range("B1:B9999").Cells
    
        If c.Interior.Color = RGB(255, 0, 0) Then f = True
    
        If IsDate(c) Then
            If c.Value < Date Then f = True
        End If
    
    Next c
    
    If f Then
        
        sh.Tab.Color = vbRed
    
    Else
        
        sh.Tab.ColorIndex = xlNone
        
    End If

Next sh

End Sub

Open in new window


This will color and un-color your sheets as you click save -so it is done as you close for when you re-open but wont effect you while you work.

You may also want to set it on open too... to check for date changes.

ATB
Steve.
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
You can utilize the Workbook Open event to make the Reports Tab Red based on your conditional formatting rule for column B.

Place the following code on ThisWorkbook Module.
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim lr As Long
Set ws = Sheets("Reports")
lr = ws.Cells(Rows.Count, 2).End(xlUp).Row
If Evaluate("sumproduct((" & ws.Name & "!B2:B" & lr & "<=today())*(" & ws.Name & "!B2:B" & lr & ">=today()-6))") > 0 Then
    ws.Tab.ColorIndex = 3
Else
    ws.Tab.ColorIndex = xlColorIndexNone
End If
End Sub

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim SchwetzWeb Specialist

Author

Commented:
Thanks for both of your fast replies,  Adding the Else statement did not work, so I tried the other comment, got an error, removed the date part, and still getting the error.  Thinking I declared them wrong?
Run-time error '438'  Object doesn't support this property or method  on yellow line in image
Run-time error '438'  Object doesn't support this property or method  on yellow line in image
Jim SchwetzWeb Specialist

Author

Commented:
Thanks Subodh Tiwari (Neeraj),
That worked perfect.  
Appreciate it.

And thanks both Rob and Steve, I learned new things trying out what you offered.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Jim! Glad it worked as desired.
Thanks for the feedback.
SteveCost Accountant
Top Expert 2012

Commented:
As for the yellow line:

You have chart sheets in your workbook which do not have ranges like B1 to B9 so the object fails.

This code only checks 'normal' sheets...

Option Explicit


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sh, f, c
For Each sh In ThisWorkbook.Sheets
    
    f = False
    
    If sh.Type = -4167 Then
    
        For Each c In sh.Range("B1:B9").Cells
        
            
        
            If c.Interior.Color = RGB(255, 0, 0) Then f = True
        
            If IsDate(c) Then
                If c.Value < Date Then f = True
            End If
        
        Next c
        
        If f Then
            
            sh.Tab.Color = vbRed
        
        Else
            
            sh.Tab.ColorIndex = xlNone
            
        End If

    End If

Next sh

End Sub

Open in new window

Jim SchwetzWeb Specialist

Author

Commented:
Thanks Steve, so I only need to check the one sheet,  But I see why it was throwing the error now.  Not sure why the -4167, works, but good to know.  I will play with it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial