Macro to colour code tab based on date on that tab

[hotspot1972]

Hi. I need to highlight the weekends on my tabs. Change the colour of the tab
I run a macro on the first day of the month and it populates the dates onto the tab names.

Private sub commnadbutton2_click
Dim shtcount as long
Dim Sarea as date, Earea as date
Sarea = InputBox("What is the start date?")
Earea = InoutBox("What is the end date?")
Shtcount = EArea - Sarea + 1
For i = 1 to shtcount
Sheets(i).Activate
If i = 1 then
Activesheet.Name = Format(SArea, "dd-mm-yy")
Else
ActiveSheet.Name = Format(SArea, "dd-mm-yy")
End if
SArea =SArea + 1
Next i
End sub

 

[Trebor76]

Hi there,

Though I don't think have a single separate tab for each day of the month is the most efficient way to go, the following will do what you're after:

Option Explicit
Private Sub commnadbutton2_click()

    Dim shtcount As Long, i As Long
    Dim Sarea As Date, Earea As Date
    
    Application.ScreenUpdating = False
    
    Sarea = InputBox("What is the start date?")
    Earea = InputBox("What is the end date?")
    
    shtcount = Earea - Sarea + 1
    
    For i = 1 To shtcount
        Sheets(i).Activate
        If i = 1 Then
            ActiveSheet.Name = Format(Sarea, "dd-mm-yy")
        Else
            ActiveSheet.Name = Format(Sarea, "dd-mm-yy")
        End If
        Select Case Weekday(ActiveSheet.Name)
            Case vbSaturday, vbSunday
                ActiveSheet.Tab.Color = RGB(0, 255, 0) 'Colour weekend tabs green. Change to suit.
        End Select
        Sarea = Sarea + 1
    Next i
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[hotspot1972]

Hi there,

Though I don't think have a single separate tab for each day of the month is the most efficient way to go, the following will do what you're after:

Option Explicit
Private Sub commnadbutton2_click()

    Dim shtcount As Long, i As Long
    Dim Sarea As Date, Earea As Date
   
    Application.ScreenUpdating = False
   
    Sarea = InputBox("What is the start date?")
    Earea = InputBox("What is the end date?")
   
    shtcount = Earea - Sarea + 1
   
    For i = 1 To shtcount
        Sheets(i).Activate
        If i = 1 Then
            ActiveSheet.Name = Format(Sarea, "dd-mm-yy")
        Else
            ActiveSheet.Name = Format(Sarea, "dd-mm-yy")
        End If
        Select Case Weekday(ActiveSheet.Name)
            Case vbSaturday, vbSunday
                ActiveSheet.Tab.Color = RGB(0, 255, 0) 'Colour weekend tabs green. Change to suit.
        End Select
        Sarea = Sarea + 1
    Next i
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

Worked a treat. Thankyou so much

 

[Trebor76]

Worked a treat. Thankyou so much

Thanks for letting us know and you're welcome