Change Tab color based on cell values in another worksheet

bear549

New Member
Joined
Aug 20, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
I have a workbook with 51 worksheets, one being a master. I would like to change the tab colors of the worksheets based on cell values on the master worksheet. There is a list of 50 cells on the master sheet that correlate to the 50 worksheets. The data in the cells on the master contains the number of students registered for a class. If the number is equal to or greater than 6, I would like the tab on the corresponding worksheet to turn green. Less than six it will be yellow.

I understand how to change the color on individual sheet sbased on cell content, but I'm struggling a bit with this one.
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Test this code by placing it in the VBE section for the "Quick" worksheet.

VBA Code:
Private Sub Worksheet_Calculate()
    Dim r As Range, c As Range
    
    Set r = Range("C5:C54")
    
    For Each c In r
        If c <> "" Then
            If c >= 6 Then
                With ActiveWorkbook.Sheets(CStr(c.Offset(0, -2).Value)).Tab
                    .Color = 5287936
                    .TintAndShade = 0
                End With
            Else
                With ActiveWorkbook.Sheets(CStr(c.Offset(0, -2).Value)).Tab
                    .Color = 65535
                    .TintAndShade = 0
                End With
            End If
        End If
    Next c

End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,801
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
VBA Code:
Sub Tab_Coloring()
'
    Dim ClassCounter        As Long
    Dim StudentCountArray() As Variant
'
    StudentCountArray = Sheets("Quick").Range("C5:C54").Value   ' Load all counts needed into an array
'
    For ClassCounter = 1 To 50
        If StudentCountArray(ClassCounter, 1) >= 6 Then Sheets(CStr(ClassCounter)).Tab.ColorIndex = 4      ' >= 6 = 4 for green
        If StudentCountArray(ClassCounter, 1) < 6 Then Sheets(CStr(ClassCounter)).Tab.ColorIndex = 6       ' Otherwise 6 for yellow
    Next
End Sub
 

bear549

New Member
Joined
Aug 20, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Crystalyzer, yours works. johnnyL, unless I did something wrong I had no results from yours.

I appreciate your help!
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,801
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Where did you run my code from?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,801
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Place the code in a separate module. Not a worksheet module.

Then when you are back to one of your sheets Click Alt-F8. In the window that pops up select 'Tab_Coloring' and then select Run
 

Forum statistics

Threads
1,147,476
Messages
5,741,349
Members
423,656
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top