Dynamically change specific tab colors using a row of cells from one sheet

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
55
Office Version
  1. 365
Platform
  1. Windows
I have a workbook with about 65 tabs/sheets. I have one specific worksheet named "CONTENTS" with a status column with dropdowns for "Not started", "Incomplete" and "Completed". Based on the dropdown value in a range of cell from D3:D58 on the sheet named "CONTENTS", I need the tab color matching the name of the A Column starting at row 3 to 58 to update with the selection. I know how to accomplish this doing it one by one with the code below, but I'm looking for a loop which can use the status from D3:D58 on the CONTENTS sheet to update each corresponding sheet. Here's the other kicker...I have a few buttons on another page to remove rows and sheets depending on the project type. So the code cannot be absolute and tied to a specific cell. So the code below wouldn't exactly work since the text "Introduction to 800-171" might not be the value of that cell if it got deleted. Please help!

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Sheets("CONTENTS").Range("D3").Value
Case "Not Started"
Sheets("Introduction 800-171").Tab.color = vbRed
Case "Incomplete"
Sheets("Introduction 800-171").Tab.color = vbYellow
Case "Completed"
Sheets("Introduction 800-171").Tab.color = RGB(0, 120, 60)
End Select
End Sub
 

Attachments

  • Mr.Excel.PNG
    Mr.Excel.PNG
    129.3 KB · Views: 10

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,349
Office Version
  1. 365
Platform
  1. Windows
That image looks as though it's A & C or B & D, as there are only 3 columns.
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
55
Office Version
  1. 365
Platform
  1. Windows
That image looks as though it's A & C or B & D, as there are only 3 columns.
OMG! You're right! I forgot that I minimized the A column so as not to show the entries there. So the sheet names are actually in Column B.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,349
Office Version
  1. 365
Platform
  1. Windows
In that case try
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Clr As Long

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("D3:D500")) Is Nothing Then
      If Target.Value <> "" Then
         If Evaluate("isref('" & Target.Offset(, -2).Value & "'!A1)") Then
            Select Case Target.Value
               Case "Not Started": Clr = 255
               Case "Incomplete": Clr = 65535
               Case "Completed": Clr = 3962880
            End Select
            Sheets(CStr(Target.Offset(, -2).Value)).Tab.Color = Clr
         Else
            MsgBox "Sheet " & Target.Offset(, -2).Value & " doesn't exist"
         End If
      End If
   End If
End Sub
 
Solution

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
55
Office Version
  1. 365
Platform
  1. Windows
In that case try
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Clr As Long

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("D3:D500")) Is Nothing Then
      If Target.Value <> "" Then
         If Evaluate("isref('" & Target.Offset(, -2).Value & "'!A1)") Then
            Select Case Target.Value
               Case "Not Started": Clr = 255
               Case "Incomplete": Clr = 65535
               Case "Completed": Clr = 3962880
            End Select
            Sheets(CStr(Target.Offset(, -2).Value)).Tab.Color = Clr
         Else
            MsgBox "Sheet " & Target.Offset(, -2).Value & " doesn't exist"
         End If
      End If
   End If
End Sub
You're a legend! IT WORKED! I've been cracking my head against the wall all weekend trying to figure this out. THANK YOU SO MUCH! One quick question. Is it possible to accomplish that the other way around? For example, if the user manually changes the tab color to red, yellow or green, then it changes the cell value to "Not Started", "Incomplete" or "Completed" respectively?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,349
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Not that I'm aware of, there is no in-built way of detecting if the colour of a tab has changed.
 

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
55
Office Version
  1. 365
Platform
  1. Windows
Not that I'm aware of, there is no in-built way of detecting if the colour of a tab has changed.
Actually...Now that I think about it, it'll probably be easier to lock the tab so they can't change the color.
 

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
55
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks again! You're awesome!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,349
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

rdoulaghsingh

Board Regular
Joined
Feb 14, 2021
Messages
55
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
No sir...Thank you! One quick observation...If I copy the contents and paste it in another cell it doesn't update the worksheet color. Is there a way to easily integrate that?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,130
Members
415,956
Latest member
Footballtend

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