Change tab color based on cell color?

Ardwinn

New Member
Joined
Nov 28, 2016
Messages
24
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?
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
Code:
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
 
Last edited:

Ardwinn

New Member
Joined
Nov 28, 2016
Messages
24
Code:
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.

Code:
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_

Well-known Member
Joined
Jul 12, 2016
Messages
512
Code:
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(#)
 
Last edited:

Ardwinn

New Member
Joined
Nov 28, 2016
Messages
24
So this thread apparently got buried so I started a second one and this was the solution Tonyyy came up with.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,525
Messages
5,659,327
Members
418,497
Latest member
VAllen79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top