CODEWITHSUNDEEP
vbaexceldate
wpeterson
wpeterson

Reputation: 21

change color of tab on sheets in a workbook based on date

I have a workbook that holds work orders(a different one on each sheet) that are time sensitive. At times there can be any where from 5 to 25 sheets in this workbook. In Cell H15 of each sheet is the due date of the work order. I am trying to set up a vba that when the workbook is opened it will check the date and change the tab of the sheets to red on the last day it is due to be done. I have a code that I thought was correct but is not working. I was told to try using (Private Sub Workbook_Open() ) but I can not seem to get the right coding so it checks all the sheets.

thank you,

Private Sub Worksheet_Change(ByVal Target As Range)

   With ActiveSheet
      If Range("H15").Value = Date Then
         .Tab.ColorIndex = 3 'Red

      End If
   End With
End Sub

Upvotes: 2

Views: 1233

Answers (1)

SJR
SJR

Reputation: 23081

Try this, in the ThisWorkbook module (and remove the worksheet change code). (In response to Jeeped's comment, the tab colour is removed everywhere else.)

Private Sub Workbook_Open()

Dim ws As Worksheet

For Each ws In Worksheets
    If ws.Range("H15").Value = Date Then
       ws.Tab.ColorIndex = 3
    Else
       ws.Tab.ColorIndex = xlColorIndexNone
    End If
Next ws

End Sub

Upvotes: 3

Related Questions