Change tab color based on changing cell value

[Ardwinn]

I created a spreadsheet that tracks the status of video conferences. The details of each video conference all feed into one master sheet that is used to manage all of these video conferences. The master sheet has a column that lists the status of each conference. [Pending, Connected, Timed Off, Ended and Pending]. I wanted to find a way to have each tab change color based on the status of the video conference on the master sheet. The code below worked, but would only change the tab color once and only for the first 3 statuses listed above. CAn anyone help me edit this code to change the tab color everytime the status on the master sheet 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

 

[My Aswer Is This]

I see one "WorkBook" here with numerous "Tabs" of different colors.

It may be possible if other workbook is the "Active" Workbook this could be happening.

Since Tony wrote this script lets wait and let him handle this situation.

He will need to know the name of the "Workbook" including extension.

If this data is being loaded automatically from some outside source the source may need to know the name of the Workbook also.

 

[tonyyy]

When Column S in the FLIGHT PLAN sheet updates (apparently automatically), the macro runs, and the active sheet gets its tabs colored. I actually think that's pretty cool... a bonus feature. But, if you want to limit the colors to just the specific workbook...

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

 

[Ardwinn]

Gentlemen, you never cease to amaze me. The code works like a charm. My tabs automatically change color, but only where I need them too. This will make tracking all my video conferences WAY easier. Thank you again for all your help. I owe you one.

 

[My Aswer Is This]

Glad we were able to help you. Come back here to Mr. Excel next time you need additional assistance.

Gentlemen, you never cease to amaze me. The code works like a charm. My tabs automatically change color, but only where I need them too. This will make tracking all my video conferences WAY easier. Thank you again for all your help. I owe you one.