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]

These are the only Vb colors:

vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, or vbWhite There is no VbOrange or VbGray You would need to use color index #

<tbody>
</tbody><colgroup><col></colgroup>

 

[Ardwinn]

I was using the Vb Orange as an example. I understand I can use the color index or .Color = RGB(255, 0, 0) to get the colors I need, however even when I used the "legal" vb colors the tabs only change color once then never again, even when the status changes.

Where I REALLY need help is making this formula change the tab color every time the status changes.

 

[My Aswer Is This]

It worked fine for me

You do know this script is only going to work on the sheets with this script installed.

Not sure why you would show a script that you know would not work and say this was only a example.

Next time show us your exact script which does not work for you.

Knowing VbOrange and Vbgray would not work should not have been in the script.

 

[Ardwinn]

I say this with all due respect. With nearly 7,000 posts under your belt you obviously know a bit about Excel. If I may however I feel as though you are coming across a bit rude...no offense. If I have done anything to instigate this then please accept my apologies. I came here for legitimate help with this issue. That said, I have edited my code to reflect the proper Vb colors so we can move past this and get to the real problem. Hopefully this will clear things up and we can move forward.

Now, if this code is placed within each sheet I wish the tab to change colors, what changes to this code need to be made in order for the tabs to change color every time the "status" (value in 'FLIGHT PLAN'!S3) changes? Right now it will do it once and that's it. Can you please tell me what I'm missing?

Thank you in advance for your help.

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


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

 

[Ardwinn]

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


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

 

[My Aswer Is This]

I think you have to do it this way.

Put this script into your Master sheet which I think must be named "'FLIGHT PLAN"

You only need this script in that sheet.

Now when you change the value in Range("S3")

Your script should do what you want on all the sheets in your workbook

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("S3")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    MyVal = Target.Value
    Dim i As Long
    
    For i = 1 To Sheets.Count
    With Sheets(i).Tab
        Select Case MyVal
            Case "Pending"
          .Color = vbCyan
            Case "Connected"
            .Color = vbGreen
            Case "Timed Off"
                .Color = vbRed
            Case "Ended"
           .Color = vbBlack
            Case "Processed"
            .Color = vbBlue
            Case Else
            .ColorIndex = xlColorIndexNone
        End Select
    End With
Next
End If
End Sub

 

[Ardwinn]

I followed your directions and tried the code you provided but nothing happened. It seems as though this would be pretty easy but so far it has completely baffled me and nothing I have tried has seemed to work.

I should also point out that on the flight plan (master sheet) there are 10 different statuses, one for each conference, so for sheet 1 the status is S3, for sheet 2 the status is in S4, and so on.

 

[My Aswer Is This]

Maybe someone else at Mr. Excel will chime in and be able to help you. I have no answer.

 

[tonyyy]

Ardwinn,

You might consider the following...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyVal As String
Dim TabNumber As Range
If Not Intersect(Target, Range("S3:S12")) Is Nothing Then
    MyVal = Target.Value
    Set TabNumber = Target.Offset(0, -17)
    With Sheets(TabNumber.Value + 1).Tab
        Select Case MyVal
            Case "Pending": .Color = vbCyan
            Case "Connected": .Color = vbGreen
            Case "Timed Off": .Color = vbRed
            Case "Ended": .Color = vbBlack
            Case "Processed": .Color = vbBlue
            Case Else: .ColorIndex = xlColorIndexNone
        End Select
    End With
End If
End Sub

The code should be copy/pasted into the Flight Plan sheet module.

Cheers,

tonyyy