View Full Version : tab colour
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
01-25-2011, 02:04 PM
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
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.
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
01-25-2011, 03:08 PM
You could use the calculate event
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.