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

 

[Ardwinn]

I greatly appreciate the effort, this is a tough one. Not that I'm all that good at excel scripts, but I have yet to find anyone that can make this work the way I am asking it to.

 

[Ardwinn]

Hi Tony, thanks for the help on this one. I followed your instructions and pasted the code into the Flight Plan module and nothing. Never changed the color of any of the tabs at all. I am wondering if instead of trying to have this run on the flight plan sheet we should be writing the script for each individual sheet instead as I did in my original post. That one at least changed the color of the tabs sometimes. Any thoughts?

 

[My Aswer Is This]

See this:
Range("S3:S12")) in the script means you have to change some value in this range:

Ardwinn. Have you ever used a sheet event script before? You do realize you have to do something correct? You cannot just post the code and then sit back and expect something to happen. You have to change the value in the range shown here:

Sheet event scripts activate when you change a value in some cell.

 

[Ardwinn]

See this:
Range("S3:S12")) in the script means you have to change some value in this range:

Ardwinn. Have you ever used a sheet event script before? You do realize you have to do something correct? You cannot just post the code and then sit back and expect something to happen. You have to change the value in the range shown here:

Sheet event scripts activate when you change a value in some cell.

Yes, I can be thick, but not that thick...lol. Of course you all have no idea of what I do and don't know about excel so totally legitimate question. To answer your question I did put in the code as I stated and then went to the 1st sheet and input dummy info into the cells that trigger the population of the "Flight Plan" sheet's status cell (S3 in this case for tab/sheet 1). I tried multiple methods to change the status in an attempt to get any of the tabs to change color but nothing worked.

If you like I can send a cop y of the spreadsheet, or if possible post a link to it here, so you all can try to fiddle around with it and see if I'm messing something up or what not?

 

[My Aswer Is This]

I just used Tony's script and when I enter "Pending" into Range("S3") the tab color changes to "Cyan"

But your saying when you do this nothing happens. Is that what you said?

And you do know your workbook must be Macro Enabled.

Do you have any other sheet event scripts in the sheet?

And the values my be exactly as shown in the script else nothing will happen.

 

[Ardwinn]

TinyUpload.com - best file hosting solution, with no limits, totaly free - This is a link to the actual spreadsheet so you all can take a look and tell me where I am going wrong. To recap I need each tab to change color based on the status of the video conference for that tab. The tab color should change every time the status of that video conference changes. Please feel free to take a look and let me know if you have any questions. THANKS!!!

 

[Ardwinn]

I have other macro's in the spreadsheet so Macros are enabled. I'm not sure if they are sheet event scripts...you can download the file on the link I posted and look for yourself. That said when I post in this script and enter dummy info into the spreadsheet in order to populate the S3:S12 the tab color doesn't change for me.

 

[My Aswer Is This]

So tell me when you installed Tony's script how did you install it.

You need to install it like this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Did you do it this way?

 

[Ardwinn]

So tell me when you installed Tony's script how did you install it.

You need to install it like this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Did you do it this way?

Yep, followed those instructions to the letter. Nothing.
Verified Macros are enabled.
Selected Flight Plan sheet
Right clicked then selected view code
Pasted code into VBA edit window


I was wondering if the reason why it wasn't working due to something else within my spreadsheet...a formula or script...that is preventing it from working. Has anyone downloaded my actual spreadsheet and tried to paste this script into it and get it to work properly?

 

[My Aswer Is This]

And when you posted in the script you saw no other script in the sheet I figure.

And your sure you entered the exact value "Pending" or what ever. There is no way on this forum for us to upload the code for you.

Yep, followed those instructions to the letter. Nothing.
Verified Macros are enabled.
Selected Flight Plan sheet
Right clicked then selected view code
Pasted code into VBA edit window


I was wondering if the reason why it wasn't working due to something else within my spreadsheet...a formula or script...that is preventing it from working. Has anyone downloaded my actual spreadsheet and tried to paste this script into it and get it to work properly?