Change worksheet tab color based on today's date

[sadi]

Hello Every Body,
I Have a worksheet that has sheets labeled 1-31 (one for each day of the month). How can I have the worksheet tab change color based on the date? For example; if when I open the workbook today is the 15th, sheet 15 would change its color tab to red. Tomorrow when I open the same workbook, I need sheet 15 tab to return to its original color and sheet 16 turn red?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks!

Sadi

 

[niceguy_jay]

you will need to add the following code to the Workbook....tell me if this works or not.....

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim i As String
i = Day(Now())
Sheets(i).Activate
With ActiveWorkbook.Sheets(i).Tab
.ColorIndex = xlNone
.TintAndShade = 0
End With
End Sub

Private Sub Workbook_Open()
Dim i As String
i = Day(Now())
Sheets(i).Activate
With ActiveWorkbook.Sheets(i).Tab
.Color = 255
End With
End Sub

 

[sadi]

niceguy_jay

Thanks for your attention. It’s work, but when I want to close the workbook it’s give a run time error 438. If I want to Debug it show .TintAndShade = 0 this line. Please help.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thanks again

 

[niceguy_jay]

the code is not giving error to me..!!
try deleting this line (TintAndShade = 0) where it is giving error...
or else if you can share the workbook i can give a closer look...

 

[Peter_SSs]

The TintAndShade issue will most likely relate to the Excel version being used. This property for worksheet tabs only appeared in Excel 2007.

 

[sadi]

Thanks guys, It's work properly after deleting (TintAndShade = 0),<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
For your information I'm using Excel 2003<o></o>