I want my sheet tabs to change color depending on the date. on the sheet

Example, each sheet represents a customer when the customer is yup for
renewal in less than 90 days to turn red, between 91 and 180 days turn orange
and beyond 180 days to turn green.

Can someone help me with this?

Neall

hi
this should work. it assumes that the date in in A2. if not change that.
post back if you need help installing.
Private Sub Workbook_Open()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
If sh.Range("A2").Value < Now + 90 Then
sh.Tab.ColorIndex = 3
Else
If sh.Range("A2").Value Now + 90 And _
sh.Range("A2").Value < Now + 180 Then
sh.Tab.ColorIndex = 45
Else
If sh.Range("A2").Value Now + 180 Then
sh.Tab.ColorIndex = 4
End If
End If
End If
Next sh
End Sub

regards
FSt1

"Neall" wrote:

I want my sheet tabs to change color depending on the date. on the sheet

Example, each sheet represents a customer when the customer is yup for
renewal in less than 90 days to turn red, between 91 and 180 days turn orange
and beyond 180 days to turn green.

Can someone help me with this?

Neall

Perfect Thanks!
--
Neall


"FSt1" wrote:

hi
this should work. it assumes that the date in in A2. if not change that.
post back if you need help installing.
Private Sub Workbook_Open()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
If sh.Range("A2").Value < Now + 90 Then
sh.Tab.ColorIndex = 3
Else
If sh.Range("A2").Value Now + 90 And _
sh.Range("A2").Value < Now + 180 Then
sh.Tab.ColorIndex = 45
Else
If sh.Range("A2").Value Now + 180 Then
sh.Tab.ColorIndex = 4
End If
End If
End If
Next sh
End Sub

regards
FSt1

"Neall" wrote:

I want my sheet tabs to change color depending on the date. on the sheet

Example, each sheet represents a customer when the customer is yup for
renewal in less than 90 days to turn red, between 91 and 180 days turn orange
and beyond 180 days to turn green.

Can someone help me with this?

Neall

I think I spoke to soon, the tab color seems to take on the color of the
current tab, each sheet has its own date in the same cell (b1) I want the
color to change depending on its own worksheet.

I have about 125 tabs

Hope you can help and thanks in advance
--
Neall


"FSt1" wrote:

hi
this should work. it assumes that the date in in A2. if not change that.
post back if you need help installing.
Private Sub Workbook_Open()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
If sh.Range("A2").Value < Now + 90 Then
sh.Tab.ColorIndex = 3
Else
If sh.Range("A2").Value Now + 90 And _
sh.Range("A2").Value < Now + 180 Then
sh.Tab.ColorIndex = 45
Else
If sh.Range("A2").Value Now + 180 Then
sh.Tab.ColorIndex = 4
End If
End If
End If
Next sh
End Sub

regards
FSt1

"Neall" wrote:

I want my sheet tabs to change color depending on the date. on the sheet

Example, each sheet represents a customer when the customer is yup for
renewal in less than 90 days to turn red, between 91 and 180 days turn orange
and beyond 180 days to turn green.

Can someone help me with this?

Neall