VBA Code For Changing Tab Color Based On Cell Values In Drop Down List

[systemsrool]

I am trying to change the color of a tab based on the cell value in B5. B5 is a drop down list using data validation that contains four entries: "Flow Rack" , "Workstation" , "Machine Guarding" , and "Other (custom)". A tab is named after each one of these as well. If someone selects "Workstation" from the drop down menu then I would like the "Workstation" tab to turn green. If any of the other three options are selected I want the "Workstation" tab to be red.

Any help is much appreciated. I only have very little experience with coding.

 

[systemsrool]

How do I do this for the three other tabs as well. If there are four options in the drop down and four corresponding tabs. I want the same function for each one.

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

I got it. I was right clicking the "Workstation" tab and doing all of this.. didn't know I was supposed to put in the code for the first tab.

 

[systemsrool]

Not sure if you saw my last reply, but how do I do this for the three other tabs as well. If there are four options in the drop down and four corresponding tabs. I want the same function for each one. I figure I could copy the code three more times, but I don't know what to change in it.

Thanks for your responses. Sorry for troubling you again.

 

[My Aswer Is This]

Not sure what your wanting now. Your original post said:
I would like the "Workstation" tab to turn green. If any of the other three options are selected I want the "Workstation" tab to be red.

Now it sounds like you want the tab to turn Green if any of these four choices are chosen is that true?

 

[systemsrool]

I was using that as an example.
There are 4 options in a drop down menu... and 4 tabs corresponding with each option.
When option 1 is picked, tab 1 is green otherwise it is red.
When option 2 is picked, tab 2 is green otherwise it is red.
When option 3 is picked, tab 3 is green otherwise it is red.
When option 4 is picked, tab 4 is green otherwise it is red.

1 = Flow Rack 2 = Workstation 3 = Machine Guarding 4 = Other (custom)

 

[My Aswer Is This]

Maybe someone else here on Mr. Excel can help you. This is now getting more complicated.
Which ever choice you make you want that tab turning Green and the other three to turn Red.
Without knowing if this is all the tabs in your workbook it makes this task very difficult for me.
It would involve a whole lot of If Else statements.

 

[My Aswer Is This]

This script will set every worksheet tab in your workbook "Red" except for the one selected in your drop down list and that one will be green.

Sub Tab_Colors()
Dim i As Integer

    For i = 1 To Sheets.Count
    Sheets(i).Tab.ColorIndex = 3
    Next
    Sheets(Cells(5, 2).Value).Tab.ColorIndex = 4
End Sub

 

[systemsrool]

Maybe this will clear things up. I don't want customer info to change color. I was using the numbers 1-4 to help you understand. But, yes whatever option in the drop down is selected should be a green tab while the others should be red. Customer info doesn't change at all. Is this possible?

 

[My Aswer Is This]

Assuming the Customer Info sheet is in the far left position on the tab menu. This script should work:

Sub Tab_Colors()
Dim i As Integer

    For i = 2 To Sheets.Count
    Sheets(i).Tab.ColorIndex = 3
    Next
    Sheets(Cells(5, 2).Value).Tab.ColorIndex = 4
End Sub