How to change sheet tab color when dropdown cell is used

[fmorenojr]

Hello,

I am trying to change the tab sheet color when a cell (that has a drop down) is picked. The drop down also has conditional formatting as well.

• Sheets are named "Student1 to Student70" (they were a copied from Student1)
• N3 is the cell that has the drop down I will be using.
• the conditional formatting is "=$N$2" and the colors for the formatting are the same as below.
• I am using a macros enabled sheet.

"Graduated" to change to Green.
"Released" to change to Red.
"Resigned" to change to Yellow.

I'm very green when it comes to VBA, I've tried codes I've seen online to mimic what I'm doing but to no avail. Any assistance is greatly appreciated.

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Put the following code in the events of Thisworkbook.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Left(Sh.Name, 7) <> "Student" Then Exit Sub
 
  If Target.Address(0, 0) = "N3" Then
    Select Case Target.Value
      Case "Graduated":  Sh.Tab.Color = vbGreen
      Case "Released":   Sh.Tab.Color = vbRed
      Case "Resigned":   Sh.Tab.Color = vbYellow
    End Select
  End If
End Sub

ThisWorkbook EVENT
- Open the VB Editor (press Alt + F11).
- Over in the Project Explorer, double click on ThisWorkbook.
- In the white panel that then appears, paste the above code.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[JoeMo]

In addition t]o coloring the tabs when the dropdown is selected, this will remove the color from the tabs if a user should delete the contents of N3 or copy and paste into N3 with something other than the 3 values you list for the dropdown.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Sh.Range("N3")) Is Nothing Then
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Select Case Target.Value
        Case "Graduated": Sh.Tab.Color = vbGreen
        Case "Released": Sh.Tab.Color = vbRed
        Case "Resigned": Sh.Tab.Color = vbYellow
        Case Else: Sh.Tab.Color = RGB(255, 255, 255)
    End Select
End If
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[fmorenojr]

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Put the following code in the events of Thisworkbook.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Left(Sh.Name, 7) <> "Student" Then Exit Sub
 
  If Target.Address(0, 0) = "N3" Then
    Select Case Target.Value
      Case "Graduated":  Sh.Tab.Color = vbGreen
      Case "Released":   Sh.Tab.Color = vbRed
      Case "Resigned":   Sh.Tab.Color = vbYellow
    End Select
  End If
End Sub

ThisWorkbook EVENT
- Open the VB Editor (press Alt + F11).
- Over in the Project Explorer, double click on ThisWorkbook.
- In the white panel that then appears, paste the above code.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Dante, this worked perfectly. I was having issues at first but it started working correctly after the fact.

Thank you so much!

FMJ

 

[JoeMo]

Dante, this worked perfectly. I was having issues at first but it started working correctly after the fact.

Thank you so much!

FMJ

What do you want to happen to the tab color if a user deletes N3 or pastes a value not on your data validation list into N3?

 

[DanteAmor]

Hi @fmorenojr

If you need what Joemo recommends, you just need one line in my code, try:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Left(Sh.Name, 7) <> "Student" Then Exit Sub
  
  If Target.Address(0, 0) = "N3" Then
    Select Case Target.Value
      Case "Graduated":  Sh.Tab.Color = vbGreen
      Case "Released":   Sh.Tab.Color = vbRed
      Case "Resigned":   Sh.Tab.Color = vbYellow
      Case Else:         Sh.Tab.ColorIndex = xlNone
    End Select
  End If
End Sub

 

[fmorenojr]

In addition t]o coloring the tabs when the dropdown is selected, this will remove the color from the tabs if a user should delete the contents of N3 or copy and paste into N3 with something other than the 3 values you list for the dropdown.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Sh.Range("N3")) Is Nothing Then
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    Select Case Target.Value
        Case "Graduated": Sh.Tab.Color = vbGreen
        Case "Released": Sh.Tab.Color = vbRed
        Case "Resigned": Sh.Tab.Color = vbYellow
        Case Else: Sh.Tab.Color = RGB(255, 255, 255)
    End Select
End If
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

I do have a blank (not the word) selection in the dropdown as well. so, to remove the color should it be

Case "Blank": Sh.Tab.Color = RGB(255, 255, 255)

 

[JoeMo]

I do have a blank (not the word) selection in the dropdown as well. so, to remove the color should it be

See post #3 or post #6 for removing the tab color when N3 is blank or contains a value not on your dropdown list.

 

[fmorenojr]

See post #3 or post #6 for removing the tab color when N3 is blank or contains a value not on your dropdown list.

Thank you. It works. Thanks again.

 

[JoeMo]

Thank you. It works. Thanks again.

You are welcome - thanks for the reply.