How to have your spreadsheet tabs change different colors automatically

[JessicaG0104]

I have created a spreadsheet of about 80 or so tabs. On each individual spreadheet, in a specific cell, I have a percentage that is populated. In another cell I have the "IF" formula to populate OFF TRACK or ON TRACK based on what the percentage is. I used this formula in the macro view code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160930
If Target.Address = "$K$12" Then
Select Case Target.Value
Case "OFF TRACK"
Me.Tab.Color = vbRed
Case "ON TRACK"
Me.Tab.Color = vbGreen
Case Else
Me.Tab.Color = vbBlue
End Select
End If
End Sub


This formula does work, but every time I go into each spreadsheet, I need to double click into the cell K12 and push enter to re-enter the IF formula to update the tab color. Is there a way to do this automatically or is something wrong with my coding? I have tried F9, alt + F9; a lot of things but I am open to trying anything else.

Thanks!
​

 

[Tetra201]

Put the following tested-working sub into the code section of each sheet whose tab you want to change color dynamically:

Private Sub Worksheet_Calculate()
Select Case Me.Range("K12").Value
    Case "OFF TRACK": Me.Tab.Color = vbRed
    Case "ON TRACK": Me.Tab.Color = vbGreen
    Case Else: Me.Tab.Color = vbBlue
End Select
End Sub

 

[JoeMo]

You might be better off with a worksheet_activate event. Delete the current _change event code and put the code below in a Thisworkbook module.

To install the code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and double-click the 'Thisworkbook' icon.
3. Copy the code below from your browser window and paste it into the white space in the VBE window.
4. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
5. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Range("K12").Value
Case "OFF TRACK"
ActiveSheet.Tab.Color = vbRed
Case "ON TRACK"
ActiveSheet.Tab.Color = vbGreen
Case Else
ActiveSheet.Tab.Color = vbBlue
End Select
End Sub

 

[HotRhodium]

Yeah no problem. The code you have here will only run "If Target.Address = "$K$12" " This looks fine but you will have to run it just about every time you change something.

Private Sub Worksheet_Change(ByVal Target As Range)
 'Updateby Extendoffice 20160930
 'If Target.Address = "$K$12" Then
 Select Case Me.Range("$K$12").Value
 Case "OFF TRACK"
  Me.Tab.Color = vbRed
 Case "ON TRACK"
  Me.Tab.Color = vbGreen
 Case Else
  Me.Tab.Color = vbBlue
 End Select
 'End If
End Sub

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu
On the far left side of the window double click on "ThisWorkbook"
Paste the code in the VBA edit window

This script will now run every time you activate any sheet in your Workbook.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Range("K12").Value = "OFF TRACK" Then ActiveSheet.Tab.Color = vbRed
If Range("K12").Value = "ON TRACK" Then ActiveSheet.Tab.Color = vbBlue
End Sub

 

[Tetra201]

@JoeMo, @My Answer Is This:

With your subs, sheet tabs will keep their previous-state color until the sheet is re-selected.

I am not a mind reader, but something tells me the user wants the tab to change color once the value of cell K12 (controlled by an IF) changes.
Also, something tells me that the sheets might have references to each other, and the user wants the non-active sheet tabs to change color as well.

Waiting for a feedback from JessicaG0104…

 

[My Aswer Is This]

My script runs when the sheet is activated which means the same thing as you said but used the word "selected" until the sheet is re-selected.

Put the following tested-working sub into the code section of each sheet whose tab you want to change color dynamically:

Private Sub Worksheet_Calculate()
Select Case Me.Range("K12").Value
    Case "OFF TRACK": Me.Tab.Color = vbRed
    Case "ON TRACK": Me.Tab.Color = vbGreen
    Case Else: Me.Tab.Color = vbBlue
End Select
End Sub