ScareBear23
New Member
- Joined
- Dec 27, 2022
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
I have a workbook that's currently a good 60-some sheets deep, and growing larger. Inside of each sheet is a different productivity tracker that has color coded values and I want the tab color to match the highest value on that sheet. I've just been manually changing each sheet to the color I want
I want a cell (C2) on each sheet to dictate the color change for the tab. I've gotten the code I want to work for a single sheet, but I want to apply it to the whole workbook so I don't need to manually add it to each current sheet & the sheets I'll be adding in the future.
This is the code that I currently have working on a single sheet. How can I make it work for all sheets? I'd class myself as fairly new to the more advanced features of Excel, so ELI5 would be greatly appreciated! Thank you!
I want a cell (C2) on each sheet to dictate the color change for the tab. I've gotten the code I want to work for a single sheet, but I want to apply it to the whole workbook so I don't need to manually add it to each current sheet & the sheets I'll be adding in the future.
This is the code that I currently have working on a single sheet. How can I make it work for all sheets? I'd class myself as fairly new to the more advanced features of Excel, so ELI5 would be greatly appreciated! Thank you!
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
If Target.Address = "$C$2" Then
Select Case Target.Value
Case "y"
Me.Tab.Color = vbYellow
Case "o"
Me.Tab.ColorIndex = 44
Case "r"
Me.Tab.Color = vbRed
Case "d"
Me.Tab.Color = RGB(192, 0, 0)
Case "p"
Me.Tab.Color = vbMagenta
Case "pr"
Me.Tab.Color = RGB(204, 0, 204)
Case "g"
Me.Tab.ColorIndex = 16
Case "b"
Me.Tab.Color = vbBlack
End Select
End If
End Sub