Automatically Color Tabs Red based on Expiration Date

P

palcallao

New Member
Joined
Oct 2, 2014
Messages
8
Greetings Mr.Excel community,

Thanks so much for all the help in advance. I have read lot of your posts and the knowledge here is amazing. Alright here is my issue, I need to color each tab red when the contents of E8:E14 and F8:F14 do not have a date posted. I have 26 tabs total in the spreasheet. Thanks again.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
I might just be missing the terminology, but what is the "date posted" criteria?

Code: 
Sub colorTabs()

    For x = 1 To ActiveWorkbook.Sheets.Count
        With Sheets(x)
            For Each cell In .Range("E8:F18")
                Select Case cell.Value
                    Case Is < Date
                        .Tab.Color = 255
                End Select
            Next cell
        End With
    Next x


End Sub
 
Upvote 0
Pretty much there is certain training members need to complete twice within a six month period. If they don't write the date in which the completed their training, I need that Tab (which is labeled with their names) to turn red. I apologize for the lack of details, I'm still learning this. Thanks for your patience.
 
Upvote 0
Oh man I tried the code you have me and it worked. Thanks!. I did realized that once they write down a date in each block, I need the tab to go back to white. Last question also, will this happen automatically? or do I have to run this macro each time the spreadsheet is opened? Thanks
 
Upvote 0
You can put this into a worksheet change event, but you'd need to place it in each worksheet. Here is modified code to color them red/white based on blank cells:

Code: 
Sub colorTabs()

    For x = 1 To ActiveWorkbook.Sheets.Count
        With Sheets(x)
            Select Case Application.WorksheetFunction.CountBlank(.Range("E8:F18"))
                Case 0
                    .Tab.Color = xlAutomatic
                Case Else
                    .Tab.Color = 255
            End Select
        End With
    Next x


End Sub
 
Upvote 0
That worked like a champ!!. The last thing, I promise. I tested it out and it worked, but it doesnt happen automatically. I have to be on VBA and click run in order to see it work. Is there a way that it will happen as soon as the criteria is met?
NeonRedSharpie said:
You can put this into a worksheet change event, but you'd need to place it in each worksheet. Here is modified code to color them red/white based on blank cells:

Code: 
Sub colorTabs()

    For x = 1 To ActiveWorkbook.Sheets.Count
        With Sheets(x)
            Select Case Application.WorksheetFunction.CountBlank(.Range("E8:F18"))
                Case 0
                    .Tab.Color = xlAutomatic
                Case Else
                    .Tab.Color = 255
            End Select
        End With
    Next x


End Sub
Click to expand...
 
Upvote 0
palcallao said:
That worked like a champ!!. The last thing, I promise. I tested it out and it worked, but it doesnt happen automatically. I have to be on VBA and click run in order to see it work. Is there a way that it will happen as soon as the criteria is met?
Click to expand...

So, like I said, you'll have to call it in each sheet. But it is possible. You have a few options:

Workbook Activate
Workbook Deactivate
Workbook Before Close

Worksheet Change
Worksheet Calculate
Worksheet Selection

If you want Worksheet Change, that would be:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    colorTabs


End Sub

And then probably change colorTabs to:

Code: 
Sub colorTabs()




        With ActiveSheet
            Select Case Application.WorksheetFunction.CountBlank(.Range("E8:F18"))
                Case 0
                    .Tab.Color = xlAutomatic
                Case Else
                    .Tab.Color = 255
            End Select
        End With




End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Change sheet tab color based on day of the week
Replies
6
Views
646
SubwaySparky
S
B
  • Question
Automatically move row to new tab when expiry date is reached
Replies
6
Views
795
brodiej
B
F
  • Question
Conditional Format multiple worksheet tabs color based on a date in each sheet
Replies
7
Views
801
kevin9999
K
J
  • Solved
Remove cell contents based on date - VBA
Replies
3
Views
465
kevin9999
K
T
  • Solved
VBA - Problem with Coding controls for values based on Dates
Replies
3
Views
628
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,214,548
Messages
6,120,146
Members
448,948
Latest member
spamiki

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top