Macro to colur tabs based on cel value

[[email protected]]

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


<[email protected]> wrote in message news:[email protected]...
> 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
>

[[email protected]]

Brilliant - Just what I needed.

Thank you.

Terry

[[email protected]]

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 ?



--
Regards Ron de Bruin
http://www.rondebruin.nl


<[email protected]> wrote in message news:[email protected]...
> Ron,
>
> Is there a way monthnum and yearnum refer to values on a sheet vs.
> changing it in the code?
>
> Thanks,
>
> Terry
>