tab colour

[wilg]

Hi, I'm trying to change the colour of the worksheet tab to yellow if a date in cell AD5 is within 7 +/- days of today(NOW())?
Only other thing to note is the date in cell AD5 is a hire date. So it may be eg "Mar 15 2001". I am trying to flag the tab by going yellow for me to reset the information in the tab because its within 7 days +/- of their anniversary..

Any help is appreciated. Thanks.

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D5" '<<<< change to suit

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If IsDate(.Value) Then

Me.Tab.ColorIndex = xlColorIndexNone
If Abs(.Value - Date) <= 7 Then

Me.Tab.Color = vbYellow
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code.
To implement it, select the sheet tab, right click, and
select View Code.
Paste this code into the code module that opens in the
VBIDE.
Then close the VBIDE and test it in Excel.

[wilg]

Firstly thanks for the quick reply. But it seems that only if "D5" changes will it go yellow. Can we not get this in a worksheet calculate event as there will be time I do not change anything on the sheet and I need it to ture yellow if within the 7 days?

[Bob Phillips]

You could use the calculate event

[vba]

Private Sub Worksheet_Calculate()

With Me.Range("D5")

If IsDate(.Value) Then

Me.Tab.ColorIndex = xlColorIndexNone
If Abs(.Value - Date) <= 7 Then

Me.Tab.Color = vbYellow
End If
End If
End With
End Sub
[/vba]