CODEWITHSUNDEEP
vbaexcel
kassinopious
kassinopious

Reputation: 33

Change Tab Colour and Periodically Refresh

I have a spreadsheet where each sheet has a value in cell C94. I want the tab colour to change depending on the value in this cell. I then want the worksheet to refresh every 60 seconds or so, and update all the tab colours in the workbook.

So far, I have this code entered on to each of the tabs:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("$C$94").Value
    Case "In Progress"
        Me.Tab.ColorIndex = 43
    Case "Missed Lay"
        Me.Tab.ColorIndex = 3
    Case "Action Required"
        Me.Tab.ColorIndex = 45
    Case "Complete"
        Me.Tab.ColorIndex = 1
    Case Else
        Me.Tab.ColorIndex = xlColorIndexNone
End Select
End Sub

This works and updates whenever I manually change data in a cell and hit enter. How do I get this to refresh the tab colour without needing to change the value and hit enter? Is it possible to get this to refresh every 60 seconds, or when data is changed in a cell in any other worksheet in the workbook? Either would be a suitable solution for my requirements.

Upvotes: 3

Views: 206

Answers (1)

paul bica
paul bica

Reputation: 10715

The most efficient way is to let Excel update all sheets

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        With ws.Tab
            Select Case ws.Range("C94").Value2
                Case "In Progress":     .ColorIndex = 43
                Case "Missed Lay":      .ColorIndex = 3
                Case "Action Required": .ColorIndex = 45
                Case "Complete":        .ColorIndex = 1
                Case Else:              .ColorIndex = xlColorIndexNone
            End Select
        End With
    Next
End Sub

There are ways to monitor changes every minute, but they will use up more resources

Upvotes: 0

Related Questions