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

 

[tonyyy]

The file download isn't responding to my click. Perhaps, Ardwinn, you can try posting it again?

 

[tonyyy]

Still haven't been able to download the sample file so the below is untested...

Private Sub Worksheet_Calculate()
Dim MyVal As String
Dim TabNumber As Range
Dim i As Long
For i = 3 To 12
    MyVal = Cells(i, 19).Value
    Set TabNumber = Cells(i, 19).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
Next i
End Sub

Delete the previous code and put the above in its place.

 

[My Aswer Is This]

Tony. Glad to see your back helping out here. I have been trying to help here but still a little confused.

Is this one script suppose to work on all sheets. Or does this script need to be put in all the sheets.[
And if only in this sheet where are the sheet names located on the Master sheet.QUOTE=tonyyy;4701615]Still haven't been able to download the sample file so the below is untested...

Private Sub Worksheet_Calculate()
Dim MyVal As String
Dim TabNumber As Range
Dim i As Long
For i = 3 To 12
    MyVal = Cells(i, 19).Value
    Set TabNumber = Cells(i, 19).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
Next i
End Sub

Delete the previous code and put the above in its place.[/QUOTE]

 

[tonyyy]

Hey MAIT...

This single script should be installed only in the Flight Plan sheet module, and will work to color all 10 sheet tabs.

From the image posted earlier in the thread, the sheet names are in Column B (thus the "Set TabNumber = Cells(i, 19).Offset(0, -17)" - which counts back from Column S 17 columns until it reaches Column B.)

I suppose I could have used "Set TabNumber = Cells(i,2)" - in fact, that would be more efficient. So Ardwinn if you're listening - go ahead and make the edit.

 

[My Aswer Is This]

Thanks Tony. I was pondering yesterday why you used:

"Cells(i, 19).Offset(0, -17)"

I see now where you see the offset was not needed.
And I never saw a image.
Hope this now works for the user.

Hey MAIT...

This single script should be installed only in the Flight Plan sheet module, and will work to color all 10 sheet tabs.

From the image posted earlier in the thread, the sheet names are in Column B (thus the "Set TabNumber = Cells(i, 19).Offset(0, -17)" - which counts back from Column S 17 columns until it reaches Column B.)

I suppose I could have used "Set TabNumber = Cells(i,2)" - in fact, that would be more efficient. So Ardwinn if you're listening - go ahead and make the edit.

 

[Ardwinn]

IT WORKS!!!! WOO HOO!!!!

Gentlemen, I can't thank you enough for your help. This code works perfectly, and on all sheets. I was listening and made the edits Tony suggested and it works great. Thank you both again so very much for your assistance. It was greatly appreciated.

 

[Ardwinn]

Ok, only one issue that I can see. If I have other excel spreadsheets open this code changes the color of that spreadsheet's tabs as well. Anyway to make this code specific only to this spreadsheet? Here is the code you provided which I have edited to suit my needs.

Private Sub Worksheet_Calculate()
Dim MyVal As String
Dim TabNumber As Range
Dim i As Long
For i = 3 To 12
    MyVal = Cells(i, 19).Value
    Set TabNumber = Cells(i, 2)
    With 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

 

[My Aswer Is This]

When you say:
If I have other excel spreadsheets open

Do you mean other "Workbooks"

In Excel we have:
"Workbooks" and "Worksheets"

The term "Spreadsheet" is a term used in Excel back in the 1980's

And where did you install this script?

The only way this script can effect another "Workbook" is if you installed the script in your "Personal" Workbook"

 

[Ardwinn]

I right clicked on the "FLIGHT PLAN" tab and selected VIEW CODE and pasted the code into the script window that appeared. When I have the workbook open with this code and I open another workbook somehow the code is changing the color of the tabs on the second workbook as well. I understand this shouldn't happen but it is. I also know about the personal workbook and the code is NOT installed there.

 

[Ardwinn]

When you say:
If I have other excel spreadsheets open

Do you mean other "Workbooks"

In Excel we have:
"Workbooks" and "Worksheets"

The term "Spreadsheet" is a term used in Excel back in the 1980's

And where did you install this script?

The only way this script can effect another "Workbook" is if you installed the script in your "Personal" Workbook"

Here's what I'm talking about: