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

[rdoulaghsingh]

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

 

[Fluff]

That image looks as though it's A & C or B & D, as there are only 3 columns.

 

[rdoulaghsingh]

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]

In that case try

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

 

[rdoulaghsingh]

In that case try

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]

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

 

[rdoulaghsingh]

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]

Thanks again! You're awesome!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[rdoulaghsingh]

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?