Change Tab color to Red based on values in first sheet

[BGJ223]

Good afternoon,


I am trying to figure out the VBA code to change the color of an excels tabs in a workbook based on values in sheet1.

For example:

Sheet 1(Intro) has Yes or No values in cells C9 through C15.
If C9 is Yes, then tab for sheet2 (Requirements) should be Red. If no then it should not change
If C10 is Yes, then tab for Sheet3 (Composition) should be Red. If no then it should not change
And so on for C11 through C15 with each referencing the next sheet in the sequence.

Any assistance would be greatly appreciated.

Using Excel 2010
Thanks

 

[My Aswer Is This]

We would need a list of all the sheet names. Using Sheet1, Sheet2 is not a good plan. Better to have names assigned.

 

[thatoneguy650]

This will change the color to red when you enter Yes in the corresponding cells. If you change it back to No it wont go back to not having a color. Put this code in the Intro sheets code

Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("C9:C15")) Is Nothing Then
    If Target.Value = "Yes" Then
        ActiveWorkbook.Sheets(Target.Row - 7).Tab.ColorIndex = 53
    End If
End If
End Sub

 

[BGJ223]

We would need a list of all the sheet names. Using Sheet1, Sheet2 is not a good plan. Better to have names assigned.

Here are the sheet names
Composition
CARS Archival
HPUB DOcuments SPecs
HPUB Print Fulfill
ECTS
Email Notification
Online

 

[My Aswer Is This]

Change Tab color to Red based on values in first sheet
Thy This:
Right Click on your Master sheet tab
Choose View code
Paste the below code:
Be sure you have all your tabs named as you gave them to me.
Composition
CARS Archival
HPUB DOcuments SPecs
HPUB Print Fulfill
ECTS
Email Notification
Online

And this is dependent on you placing the word "Yes" in cells C9 to C15
The word no is not required. Removing the word "Yes" will do the reset.
You can put the word "No" in the cell if you wish.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("C9") = "Yes" Then
Range("C9").Interior.ColorIndex = 3
Worksheets("Composition").Tab.ColorIndex = 3
Else
Range("C9").Interior.ColorIndex = xlNone
Worksheets("Composition").Tab.ColorIndex = xlNone
End If
If Range("C10") = "Yes" Then
Range("C10").Interior.ColorIndex = 3
Worksheets("CARS Archival").Tab.ColorIndex = 3
Else
Range("C10").Interior.ColorIndex = xlNone
Worksheets("CARS Archival").Tab.ColorIndex = xlNone
End If
If Range("C11") = "Yes" Then
Range("C11").Interior.ColorIndex = 3
Worksheets("HPUB DOcuments Specs").Tab.ColorIndex = 3
Else
Range("C11").Interior.ColorIndex = xlNone
Worksheets("HPUB DOcuments Specs").Tab.ColorIndex = xlNone
End If
If Range("C12") = "Yes" Then
Range("C12").Interior.ColorIndex = 3
Worksheets("HPUB Print Fulfill").Tab.ColorIndex = 3
Else
Range("C12").Interior.ColorIndex = xlNone
Worksheets("HPUB Print Fulfill").Tab.ColorIndex = xlNone
End If
If Range("C13") = "Yes" Then
Range("C13").Interior.ColorIndex = 3
Worksheets("ECTS").Tab.ColorIndex = 3
Else
Range("C13").Interior.ColorIndex = xlNone
Worksheets("ECTS").Tab.ColorIndex = xlNone
End If
If Range("C14") = "Yes" Then
Range("C14").Interior.ColorIndex = 3
Worksheets("Email Notification").Tab.ColorIndex = 3
Else
Range("C14").Interior.ColorIndex = xlNone
Worksheets("Email Notification").Tab.ColorIndex = xlNone
End If
If Range("C15") = "Yes" Then
Range("C15").Interior.ColorIndex = 3
Worksheets("Online").Tab.ColorIndex = 3
Else
Range("C15").Interior.ColorIndex = xlNone
Worksheets("Online").Tab.ColorIndex = xlNone
End If
End Sub

 

[My Aswer Is This]

You did not say you wanted the names of the tabs in C9 to C15 so I did not make it that way. But I do have a modified version that would do that if you want that.