Change tab color based on cell color?

[Ardwinn]

Hello all,

I have a bit of a challenge. I have a spreadsheet that has 11 tabs, one "master" tab and 10 others that feed information into the master tab. The master tab keeps track of meetings and highlights each row on the master sheet depending on the status of the meeting (Pending, Connected, Timed off, Ended and Processed). I would like each tab to change color based on that particular meetings status. For example if the meeting on tab 1 is in progress then row 1 of the master tab changes from orange (Pending) to green (Connected) tab 1 should also change color. When meeting 1 ends (Timed Off), row 1 on the master tab turns red. I would like tab one to turn red. I was thinking it would have to be a script that looked at the master sheet for the status of that meeting and assigned the correct color based on the value it finds on the master tab for the particular meeting. I had a script that did this, however it only changed the color once and only if I edited the meeting start and end times. I would like this script to change the tab color every time the status changes on the master tab for that particular meeting. Does that make sense?

 

[Tim_Excel_]

Sub Workbook_SheetChange(ByVal Sh As Object, _
 ByVal Source As Range)

If Range("A1").Interior.Color = RGB(0, 255, 0) Then
Sheets("Sheet1").Tab.Color = RGB(0, 255, 0)
Else
Sheets("Sheet1").Tab.Color = RGB(255, 0, 0)
End If
End Sub

I hope this works. Paste this in the ThisWorkbook tab in VBA. The color green in the sheet should be RGB(0,255,0), otherwise it won't work.
Also, I believe this code only works when anything chances in the worksheet, but not when a color chances. So it'd have to be text

 

[Ardwinn]

Sub Workbook_SheetChange(ByVal Sh As Object, _
 ByVal Source As Range)

If Range("A1").Interior.Color = RGB(0, 255, 0) Then
Sheets("Sheet1").Tab.Color = RGB(0, 255, 0)
Else
Sheets("Sheet1").Tab.Color = RGB(255, 0, 0)
End If
End Sub

I hope this works. Paste this in the ThisWorkbook tab in VBA. The color green in the sheet should be RGB(0,255,0), otherwise it won't work.
Also, I believe this code only works when anything chances in the worksheet, but not when a color chances. So it'd have to be text

Below is the script I used with limited success, excel wouldn't accept the 'FLIGHT PLAN'!S3 part so I had to add a formula to tab 1 that made cell L10='FLIGHT PLAN'!S3 in order to get it to work. Also it would only update the tab color once however and it I need it to update every time the status changes.

Private Sub Worksheet_Change(ByVal Target As Range)
    MyVal = Range("'FLIGHT PLAN'!S3").Text


    With ActiveSheet.Tab
        Select Case MyVal
            Case "Pending"
                .Color = vbOrange
            Case "Connected"
                .Color = vbGreen
            Case "Timed Off"
                .Color = vbRed
            Case "Ended"
                .Color = vbGray
            Case "Processed"
                .Color = vbBlue
            Case Else
                .ColorIndex = xlColorIndexNone
        End Select
    End With
End Sub

 

[Tim_Excel_]

Private Sub Worksheet_Change(ByVal Target As Range)
    MyVal = Range("L10").Text


    With ActiveSheet.Tab
        Select Case MyVal
            Case "Pending"
                .Color = RGB(250, 100, 0)
            Case "Connected"
                .Color = vbGreen
            Case "Timed Off"
                .Color = vbRed
            Case "Ended"
                .Color = vbGray
            Case "Processed"
                .Color = vbBlue
            Case Else
                .ColorIndex = xlColorIndexNone
        End Select
    End With
End Sub

The code works fine for me, except for the colour orange not working in your code, so I changed it to RGB. Are you sure you have the code in your sheet's VB? Every time I change the status myself the sheet's tab colour changes, so maybe it has to do something with your other code updating the text. Also I'd be cautious with using ActiveSheet, since I understand you have multiple sheets I'd recommend you to go for Sheets(#)

 

[Ardwinn]

So this thread apparently got buried so I started a second one and this was the solution Tonyyy came up with.

Private Sub Worksheet_Calculate()
Dim MyVal As String
Dim TabNumber As Range
Dim i As Long
For i = 3 To 12
    MyVal = ThisWorkbook.Sheets("FLIGHT PLAN").Cells(i, 19).Value
    Set TabNumber = ThisWorkbook.Sheets("FLIGHT PLAN").Cells(i, 2)
    With ThisWorkbook.Sheets(TabNumber.Value + 1).Tab
        Select Case MyVal
            Case "Pending": .Color = RGB(255, 192, 0)
            Case "Connected": .Color = RGB(0, 176, 80)
            Case "Timed Off": .Color = RGB(255, 0, 0)
            Case "Ended": .Color = RGB(89, 89, 89)
            Case "Processed": .Color = RGB(139, 225, 255)
            Case Else: .Color = vbBlack
        End Select
    End With
Next i
End Sub

 

[Tim_Excel_]

Glad it worked out for you!