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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Could you adapt something like this to suit your needs?

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

End Sub
 

bear549

New Member
Joined
Aug 20, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
I guess I should have provided a more information. The sheet name with range of cells (C5:C54) is "Quick". Each corresponding tab is named/numbered "1 to 50". Does the code go on sheet "Quick"?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,803
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@bear549
1) Can you take a screen pic of your workbook after you hit Alt-F11 and post it here?
2) You said 'C5:C54'. Does C5 correspond to the 1st sheet of the 50 other sheets, C6 correspond to the 2nd sheet of the 50 other sheets and so on?
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

is this what your "Quick" worksheet looks like?

bear549 20210820.xlsm
ABCD
1
2
3
4ClassNumber of Students
514
626
734
842
Master2
Cell Formulas
RangeFormula
D5D5='1'!A1
D6D6='2'!A1
D7D7='3'!A1
D8D8='4'!A1


If so, you can place this code into the "Quick" worksheet VBE
VBA Code:
Private Sub Worksheet_Calculate()
    Dim r As Range, c As Range
    
    Set r = Range("D5:D54")
    
    For Each c In r
        If c <> "" Then
            If c >= 6 Then
                With ActiveWorkbook.Sheets(CStr(c.Offset(0, -1).Value)).Tab
                    .Color = 5287936
                    .TintAndShade = 0
                End With
            Else
                With ActiveWorkbook.Sheets(CStr(c.Offset(0, -1).Value)).Tab
                    .Color = 65535
                    .TintAndShade = 0
                End With
            End If
        End If
    Next c

End Sub
 
Last edited:

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,803
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
I can do it in 1/2 the code as soon as the questions are answered from posts #4 and #5. :)
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I didn't realize this was "Name That Code"
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,803
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
I didn't realize this was "Name That Code"
HeHeHe. I am in between projects right now so I am bored. :)

Just having some fun.

7 lines of code in between the Sub/EndSub. All lines separate.
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,803
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Btw @Crystalyzer, please make another post somewhere quickly, your current post count = 666 :eek:
 

bear549

New Member
Joined
Aug 20, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
I've added three screenshots: The code, the Master sheet, and the Quick sheet. Yes, C5 corresponds to Sheet 1, etc.



Screen Shot 1.jpg
Screen Shot 2.jpg
Screen Shot 3.jpg
 

Forum statistics

Threads
1,147,482
Messages
5,741,406
Members
423,657
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