Change color on tab

[KlausW]

Hi I have a vague plan divided into months, I have tried with this VBA to get the color of the tab as the date you are in now to change the Danish name of the month is in cell E1, but it may not need to happen. The VBA code is in this production's directory, but it's not what's wrong, anyone can help?

Private Sub Workbook_Open ()


Dim WS As Worksheet

Dim MountNu As String

MountNu = Range ("E1") & WorksheetFumktion.MountNow ()

For Each WS In ThisWorkbook.Worksheets

If WS.Name = MountNu Then

WS.Tab.ColorIndex = 4

Else

WS.Tab.ColorIndex = xlColorIndexNone

End If

Next


End Sub

All help will be appreciated

Regards Klaus W

 

[Fluff]

Are the tab names the full month name or are they three letter abbreviations?

 

[KlausW]

Are the tab names the full month name or are they three letter abbreviations?

It's the full month's name.

 

[Fluff]

Ok, how about

Private Sub Workbook_Open()


Dim WS As Worksheet

Dim MountNu As String

MountNu = MonthName(Month(Date))

For Each WS In ThisWorkbook.Worksheets

If WS.Name = MountNu Then

WS.Tab.ColorIndex = 4

Else

WS.Tab.ColorIndex = xlColorIndexNone

End If

Next


End Sub

 

[KlausW]

Ok, how about

Private Sub Workbook_Open()


Dim WS As Worksheet

Dim MountNu As String

MountNu = MonthName(Month(Date))

For Each WS In ThisWorkbook.Worksheets

If WS.Name = MountNu Then

WS.Tab.ColorIndex = 4

Else

WS.Tab.ColorIndex = xlColorIndexNone

End If

Next


End Sub

And I put in this workbook ?

 

[Fluff]

That's right & it will run whenever you open the workbook.

 

[KlausW]

That's right & it will run whenever you open the workbook.

nothing is happening, the name of the month is in the Tab

 

[Fluff]

If you add the message box as shown

MountNu = MonthName(Month(Date))
MsgBox MountNu
For Each WS In ThisWorkbook.Worksheets

what does it return?

 

[KlausW]

If you add the message box as shown

MountNu = MonthName(Month(Date))
MsgBox MountNu
For Each WS In ThisWorkbook.Worksheets

what does it return?

It is returning the rigth month "May"

 

[Fluff]

But is it in Danish, assuming that's how the sheets are named?