# Change Tab color based on cell values in another worksheet

#### bear549

##### New Member
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

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
End With
Else
With ActiveWorkbook.Sheets(c.Offset(0, -1).Value).Tab
.Color = 65535
End With
End If
Next c

End Sub``````

#### bear549

##### New Member
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
@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

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
End With
Else
With ActiveWorkbook.Sheets(CStr(c.Offset(0, -1).Value)).Tab
.Color = 65535
End With
End If
End If
Next c

End Sub``````

Last edited:

#### johnnyL

##### Well-known Member
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

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

#### johnnyL

##### Well-known Member
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
Btw @Crystalyzer, please make another post somewhere quickly, your current post count = 666

#### bear549

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

Replies
1
Views
289
Replies
0
Views
587
Replies
14
Views
541
Replies
5
Views
104
Replies
3
Views
152

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.

### Which adblocker are you using?

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

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