Change Tab color based on cell values in another worksheet

[bear549]

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]

Could you adapt something like this to suit your needs?

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]

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]

@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]

is this what your "Quick" worksheet looks like?

bear549 20210820.xlsm
	A	B	C	D
1
2
3
4			Class	Number of Students
5			1	4
6			2	6
7			3	4
8			4	2
Master2

Cell Formulas
Range	Formula
D5	D5	='1'!A1
D6	D6	='2'!A1
D7	D7	='3'!A1
D8	D8	='4'!A1

If so, you can place this code into the "Quick" worksheet VBE

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

 

[johnnyL]

I can do it in 1/2 the code as soon as the questions are answered from posts #4 and #5.

 

[Crystalyzer]

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

 

[johnnyL]

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]

Btw @Crystalyzer, please make another post somewhere quickly, your current post count = 666

 

[bear549]

I've added three screenshots: The code, the Master sheet, and the Quick sheet. Yes, C5 corresponds to Sheet 1, etc.