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]

Test this code by placing it in the VBE section for the "Quick" worksheet.

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

 

[johnnyL]

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]

Crystalyzer, yours works. johnnyL, unless I did something wrong I had no results from yours.

I appreciate your help!

 

[johnnyL]

Where did you run my code from?

 

[bear549]

Where did you run my code from?

I put it in the VBE of the"Quick" sheet.

 

[johnnyL]

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