Macro for tab colors based on column value

[Kayjay88]

Hi all,

I'm trying to format my tab colors based on cell value in column I.
Example:
"At Risk" - Red Tab
"In Progress" - Green Tab
"Not Started" - Yellow Tab

I need it to apply to all worksheets, range for all worksheets is I9:I30
All the macros I have tried don't seem to help me.
Please help!!

Thank in advance
Kay

 

[My Aswer Is This]

I need more specific details.

So if what cell on the sheet says:

At Risk
Will cause this Tab color to be Red

And the same for others:

And why are you using Range("I9:130")

But you are only using three colors.

And how to you want this script to be activated?
​

 

[Kayjay88]

Each row is a project activity (the worksheets are different projects), column I is the indicator of whether the activity is "in progress", "at risk" etc.
There are headers which have other information from row 1-8 and the status starts from row 9 onward.
I would like the script to be activated when the status is selected.

Hope this answers your questions.

 

[My Aswer Is This]

So if the value in Range("I9") is changed to

at risk The Tab color is changed to "Red"
What happens when Range("I14") is changed to at risk is the tab color changed to "Red"

What happens when Range("I25") is changed to at risk is the tab color changed to "Red"​

What does this mean?

I would like the script to be activated when the status is selected.​

Do you mean when the cell value is manually changed?
<strike></strike>
​

 

[Kayjay88]

The priority of the status' would be Red, Yellow, Green ("At Risk", "Not Started", "In Progress")
If "At risk" is selected in the range I want the tab to change to red (regardless of the other status'), then "Not Started", and so on.
Yes, when the status is selected manually.

Sorry for the incomplete and confusing information.
Thank you!

 

[My Aswer Is This]

I asked:

Do you mean when the cell value is manually changed?​

You said:

selected manually

Change and selected is not the same.

Is this a column with a Data validation list?

And you will select a value from the Data Validation list.
​

 

[Kayjay88]

Correct, sorry. The status is manually selected from a validation list.

 

[My Aswer Is This]

Try this for one sheet and see if it does what you want.

If it works for one sheet I will then tell you how to have it work on all sheets
This script works from Range("I9") and below

This is an auto sheet event script
Your Workbook must be Macro enabled
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)
'Modified  5/8/2019  10:17:44 PM  EDT
If Target.Column = 9 And Target.Row > 8 Then
Select Case Target.Value
    Case "At Risk"
        ActiveSheet.Tab.Color = vbRed
    Case "In Progress"
        ActiveSheet.Tab.Color = vbGreen
    Case "Not Started"
        ActiveSheet.Tab.Color = vbYellow
End Select
End If
End Sub

 

[Kayjay88]

Works perfectly!! Thank you so much!

 

[My Aswer Is This]

So if you want this same script to work on all sheets:
Do this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu
Double click on This Workbook you will see this on far left top corner
Paste this code in the VBA edit window

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  5/8/2019  11:05:44 PM  EDT
If Target.Column = 9 And Target.Row > 8 Then
Select Case Target.Value
    Case "At Risk"
        ActiveSheet.Tab.Color = vbRed
    Case "In Progress"
        ActiveSheet.Tab.Color = vbGreen
    Case "Not Started"
        ActiveSheet.Tab.Color = vbYellow
End Select
End If
End Sub