Macro to colur tabs based on cel value

[cvasquez]

Need help in developing a macro that will colour the sheet tab based on
the sheet name or cell value in a sheet that is equal to, for example,
saturday or sunday. For example, Oct 2, 2005 - SUN. I have 31 sheets
for each day of the month, named 1, 2, 3, etc... Having the sheets
colour coded just makes it visually easier to select the proper sheet.

Any suggestions would be appreciated.

Thanks you.

Terry

 

[Ron de Bruin]

You can try this example with sheets named 1 ,2 ......

Sub test()
Dim sh As Worksheet
Dim monthnum As Integer
Dim yearnum As Integer

monthnum = 10
yearnum = 2005

For Each sh In ActiveWorkbook.Worksheets
If sh.Visible = -1 Then
If Application.WorksheetFunction.Weekday(DateSerial(yearnum, monthnum, sh.Name), 2) > 5 Then
sh.Tab.ColorIndex = 3
Else
sh.Tab.ColorIndex = -4142
End If
End If
Next sh
End Sub

 

[cvasquez]

Brilliant - Just what I needed.

Thank you.

Terry

 

[cvasquez]

Ron,

Is there a way monthnum and yearnum refer to values on a sheet vs.
changing it in the code?

Thanks,

Terry

 

[Ron de Bruin]

Hi Terry

Is there a way monthnum and yearnum refer to values on a sheet vs.
changing it in the code?

Sure

monthnum = Sheets("1").Range("A1")
yearnum = Sheets("1").Range("A2")

Maybe you must change the sheet name ?