Tab Color changes based on data from another Static Sheet

MBellew1463

New Member
Joined
Jun 8, 2022
Messages
10
Office Version
  1. 2021
Platform
  1. Windows
I wanted to see if the tab color can be changed based on data from another sheet. Sheets 1 through 100 will have data fed from another page within the workbook but the formatting needs to be done from the main page as sheets 1-100 change weekly and are imported from a separate report.

Basically want to change the tab color to RED of any sheet where the value in the the main sheet(JSR SUMMARY) = Forecast as follows

EDIT:
IF "JSR SUMMARY" sheet T4 = "Reforecast as Follows" then change tab color of Sheet1 to RED
 
Last edited by a moderator:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
If I've get correctly... Copy to the ThisWorkbook module:
VBA Code:
Private Sub Workbook_Open()
Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        If sh.Range("T4").Value = "Reforecast as Follows" Then
            sh.Tab.ColorIndex = 3
        Else
            sh.Tab.ColorIndex = -4142
        End If
    Next sh
End Sub
It changes tab color after workbook opening.
 
Upvote 0
If I've get correctly... Copy to the ThisWorkbook module:
VBA Code:
Private Sub Workbook_Open()
Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        If sh.Range("T4").Value = "Reforecast as Follows" Then
            sh.Tab.ColorIndex = 3
        Else
            sh.Tab.ColorIndex = -4142
        End If
    Next sh
End Sub
It changes tab color after workbook opening

If I've get correctly... Copy to the ThisWorkbook module:
VBA Code:
Private Sub Workbook_Open()
Dim sh As Worksheet
    For Each sh In ThisWorkbook.Worksheets
        If sh.Range("T4").Value = "Reforecast as Follows" Then
            sh.Tab.ColorIndex = 3
        Else
            sh.Tab.ColorIndex = -4142
        End If
    Next sh
End Sub
It changes tab color after workbook opening.
This works FLAWLESSLY! What would be entered to have it look for another value in the same cell as well and change it the tab to blue if it was "CLOSE JOB" ?
 
Upvote 0
What would be entered to have it look for another value in the same cell
Paste before line
VBA Code:
Next sh
in my previous code this:
VBA Code:
        If sh.Range("T4").Value = "CLOSE JOB" Then
            sh.Tab.ColorIndex = 34
        Else
            sh.Tab.ColorIndex = -4142
        End If
 
Upvote 0
If sh.Range("T4").Value = "CLOSE JOB" Then sh.Tab.ColorIndex = 34 Else sh.Tab.ColorIndex = -4142 End
That doesn't seem to work - I think because of the else argument. When entered as directed it changes them briefly to the color and then back to no color. I am using this code in a macro button... I have no problem with just the one value lookup and change but not working when looking for a second value as well.
 
Upvote 0
That doesn't seem to work - I think because of the else argument. When entered as directed it changes them briefly to the color and then back to no color. I am using this code in a macro button... I have no problem with just the one value lookup and change but not working when looking for a second value as well.
Correct, my fault, didn't test it. Try:
VBA Code:
Private Sub Workbook_Open()
Dim sh As Worksheet, g4$
    For Each sh In ThisWorkbook.Worksheets
        g4 = sh.Range("D4").Value
        If g4 = "Reforecast as Follows" Then
            sh.Tab.ColorIndex = 3
        ElseIf g4 = "CLOSE JOB" Then
            sh.Tab.ColorIndex = 34
        Else
            sh.Tab.ColorIndex = -4142
        End If
    Next sh
End Sub
 
Upvote 0
Solution
Correct, my fault, didn't test it. Try:
VBA Code:
Private Sub Workbook_Open()
Dim sh As Worksheet, g4$
    For Each sh In ThisWorkbook.Worksheets
        g4 = sh.Range("D4").Value
        If g4 = "Reforecast as Follows" Then
            sh.Tab.ColorIndex = 3
        ElseIf g4 = "CLOSE JOB" Then
            sh.Tab.ColorIndex = 34
        Else
            sh.Tab.ColorIndex = -4142
        End If
    Next sh
End Sub
PERFECT!! You got me at first until I realized g4 = sh.Range("D4").Value should have been ("G4")! :) Works like a charm!!
 
Upvote 0
PERFECT!! You got me at first until I realized g4 = sh.Range("D4").Value should have been ("G4")! :) Works like a charm!!
Yeah, careless mistake... :) Glad I can help & thanks for the feedback.
 
Upvote 0
PERFECT!! You got me at first until I realized g4 = sh.Range("D4").Value should have been ("G4")! :) Works like a charm!!
The solution post has been changed accordingly.

@MBellew1463, in your future questions, please mark the post as the solution that answered your question to help future readers.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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
Back
Top