Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet Tab Conditional Formatting According To Date

    I have a spreadsheet that I use to enter data on a daily basis for each month. I have 31 worksheet tabs numbered 1-31. The last worksheet tab is Summary. On the Summary worksheet I enter the first day of the month, then copy down to create dates for the complete month. On the numbered worksheets, Cell A1 refers to the corresponding date, ie worksheet 1 (tab 1) A1 = A1 on Summary, which is June 1, 2015,worksheet 2 is June 2, 2015,etc.

    I would like each tab to be colored blue if it is a Saturday, or yellow if it is a Sunday,taking the date from cell A1 on each respective sheet. All other days would remain the default color.

    Any help to accomplish this would be greatly appreciated.

    Thanks,
    Craig

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Craig,

    Welcome to the Lounge as a New Poster!

    Here's some code that will do the trick.
    Code:
    Option Explicit
    
    Public Sub ColorTabs()
    
       Dim iWorkDay          As Integer
       Dim sht               As Worksheet
       
       For Each sht In ActiveWorkbook.Sheets
       
          If UCase(sht.Name) = "SUMMARY" Then
          Else
            iWorkDay = WorksheetFunction.Weekday(sht.[A1])
          
            Select Case iWorkDay
          
                Case 1
                    sht.Tab.Color = vbYellow
                Case 7
                    sht.Tab.Color = vbBlue
                Case Else
                    sht.Tab.Color = vbWhite
            End Select
            
          End If
          
       Next sht
       
    End Sub       'ColorTabs
    ColorTabs.JPG

    Just paste code into a Standard Module and save file as .xlsm or .xlsb and you're good to go.

    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
    May 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your quick reply, that worked perfect!

    Craig

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Craig

    Sorry RetiredGeek but Craig wants all non Saturday or Sunday worksheets to be "default color", so all I'll change is last line in the case statement from sht.Tab.Color = vbWhite to sht.Tab.Color = xlAutomatic. And I think that should do the trick. The code is nice and neat. Add some comments if you are new to VBA, so that you can remember 6 months from now what the heck it all does.

    Cheers to both of you.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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