Worksheet tabs to turn colors against criteria

  • Thread starter Thread starter Neall
  • Start date Start date
N

Neall

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
 
Perfect Thanks!
--
Neall


FSt1 said:
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
Click to expand...
 
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
 
Back
Top