rdoulaghsingh
Board Regular
- Joined
- Feb 14, 2021
- Messages
- 105
- Office Version
- 365
- Platform
- 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
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