Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Color code tabs by date

    Hello All,

    I am developing a workbook in excel 2010 that many people with a wide variety of skills will be using. My workbook has 31 tabs (1 for each day of the month). I have cell E2 set to display the date in a *Wednesday, March 14, 2001 format. Since the spreadsheets for Saturday are used differently than the ones for Monday - Friday and Sunday should not be used at all; I was wondering if there is a way to have excel automatically color code the corresponding tabs so that Saturdays would have a yellow tab and Sundays would have a red tab.

    Any help in this would be greatly appreciated,
    Thanks, Keith

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Keith,

    This code should do the trick. Note: if you have sheets that shouldn't be colored you'll need to modify the code to exempt them.
    Code:
    Option Explicit
    
    Sub ColorTabs()
    
       Dim wks As Worksheet
       
       For Each wks In ActiveWorkbook.Sheets
       
         Select Case WorksheetFunction.Weekday(wks.[e2], 1)
            Case 1
              wks.Tab.Color = 255
            Case 7
              wks.Tab.Color = 65535
            Case Else
              wks.Tab.Color = xlAutomatic
            
          End Select
       Next wks
       
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Jul 2014
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks much! That was exactly what I was looking for. It works great!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •