vba quest tabcolor dependent on actual weeknumber

[ikke]

Dear All,


Info:
a. excel file with a lot of tabs.
b. Most tabs have already a (week) number generated from vba and then:

I am looking for a working VBA Excel code that generates the following:

based on the current week I want to have a color for the relevant tab.
The color choice is varias (unimportant).

1. Should the code be in the "this workbook"? It's has to be automatic, i suppose?
2. Can you help me get started?

already many thanks

 

[Cubist]

What are the tab names?

 

[ikke]

With extensive VBA code that controls much more, I always give a newly created tab a number that corresponds to a weeknumber, ascending. so each week has its own tab with weeknumber. It is useful for the user(s) to see what week it is. I just can't make the link with that week number and generate color. Hopefully the challenge is a bit clearer.

 

[Cubist]

With extensive VBA code that controls much more, I always give a newly created tab a number that corresponds to a weeknumber, ascending. so each week has its own tab with weeknumber. It is useful for the user(s) to see what week it is. I just can't make the link with that week number and generate color. Hopefully the challenge is a bit clearer.

Take today as an example (4/27/24). How do you determine the week number? What I'm asking is when is week number 1?

 

[ikke]

whit this :

Sheets("***").Select
Set NewSheet = Worksheets.Add

NewSheet.Name = Worksheets("***").Range("week1").Value


*** =private isue, not important for this.
but see belowe the name (number) for example.

 

[Cubist]

See if this is what you want. Place the code into the "ThisWorkBook" module. Test by clicking through different tabs.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim ws As Worksheet
    Dim maxTab As Worksheet
    Dim maxNum As Integer
    Dim wsNum As Integer
   
    maxNum = 0
    Set maxTab = Nothing
   
    For Each ws In ThisWorkbook.Worksheets
        wsNum = Val(ws.Name)
        If wsNum > maxNum Then
            maxNum = wsNum
            Set maxTab = ws
        End If
    Next ws
   
    For Each ws In ThisWorkbook.Worksheets
        ws.Tab.ColorIndex = xlColorIndexNone
    Next ws
   
    maxTab.Tab.Color = vbYellow
End Sub

 

[ikke]

Thank you for your time.
this code colors the last tab (23) yellow.
I would like the code based on the week number (it is now week18) to change color on this tab (18) when opening this file. next week 19 and so on.

 

[Cubist]

I've been asking you about the starting point i.e. week 1 but it looks like you're referring to the week of the calendar year. Try this.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim ws As Worksheet
    Dim maxTab As Worksheet
    Dim currentWeek As Integer
    
    ' Get the current ISO week number
    currentWeek = WorksheetFunction.ISOWEEKNUM(Date)
    
    Set maxTab = Nothing
   
    For Each ws In ThisWorkbook.Worksheets
        If Val(ws.Name) = currentWeek Then
            Set maxTab = ws
            Exit For
        End If
    Next ws
   
    For Each ws In ThisWorkbook.Worksheets
        ws.Tab.ColorIndex = xlColorIndexNone
    Next ws
   
    If Not maxTab Is Nothing Then
        maxTab.Tab.Color = vbYellow
    End If
End Sub

 

[ikke]

Dear Cubist, I would like to thank you very much, this works exactly as I want. Thank you again for your time.