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.

 

[My Aswer Is This]

Try this:
To install this code:

Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5")) Is Nothing Then
If Cells(5, 2).Value = "Workstation" Then
Sheets("Workstation").Tab.ColorIndex = 4
Else
Sheets("Workstation").Tab.ColorIndex = 3
End If
End If
End Sub

 

[systemsrool]

This didn't work. Just clarify I'm using excel 2013.

 

[My Aswer Is This]

Did you insert this script in your sheet? It does not get put in a module.
Are you sure the spelling is the same. You said "Workstation" not "WorkStation" and not "Work Station"

 

[systemsrool]

I did the steps that you listed above the code. Right the sheet, view code, copy/paste, and then saved that. Am I missing something? and everything is spelled exactly the same.

 

[My Aswer Is This]

Have you run macros on your sheet before?
Does your workbook name end with .xlsm.
And is your Workbook allowed to run Macros?
And the value in cell B5 is "Workstation"

 

[systemsrool]

Yes and it saved with .xlsm.
Yes. It's not saving as a macro from what I can tell.
I debugged and clicked run and it comes up with a prompt about naming the macro.

 

[My Aswer Is This]

What do you mean you clicked run.
This is not a Macro that you run from the macro recorder.
This macro runs when you change the value in cell B5.
That's why I said this macro does not go into a module.
If you right clicked the sheet tab and then choose view code and then choose insert and put this script into a module it will not run.
You must right click the Workstation tab choose view code and paste it into the window you see. Not in a module. And then close the window change the value in B5 and your tab color will change

 

[systemsrool]

I did just as you said in the last line and it's not doing anything.
Does this work if B5 is a dropdown list using data validation?

 

[systemsrool]

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.