How Can I Change Tab Color if Any Change is Made to a Worksheet?

I have created a Work Slip in Excel.  I have broken it into separate worksheets so it doesn't overwhelm the user having everything on one sheet.

I want to create a macro that will change the tab color for a sheet if that sheet is revised in any way.  I found some code that changes the tab color based on a cell value, so I think this is possible, but I am not sure how to make it trigger if anything on the sheet changes.

Code that changes the tab color based on a cell value (as an example):

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B9").Value <> 0 Then
    Me.Tab.ColorIndex = 3
Else
    Me.Tab.ColorIndex = xlColorIndexNone
End If
End Sub
Dave KongAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
This will change the tab colour when anything changes...

Private Sub Worksheet_Change(ByVal Target As Range)
     Me.Tab.ColorIndex = 3
End Sub

Open in new window

0
Dave KongAuthor Commented:
That is elegantly simple!  I will try this out as soon as I can.  Thank you!
0
Dave KongAuthor Commented:
Question:  Where exactly do I put this code?

Dave
0
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Wayne Taylor (webtubbs)Commented:
Right-click on the sheet tab and select "View code". Put the code in this module.
0
Dave KongAuthor Commented:
I put the code in the module.  When is it supposed to trigger?  I made changes to that sheet but the tab did not change.
0
Wayne Taylor (webtubbs)Commented:
It will change whenever a cell's value is changed. Attached is an example. I've also put code into the ThisWorkbook module which clears all tab colours when the workbook is saved.
E--Book1.xlsm
0
Dave KongAuthor Commented:
I see it working now!  I was originally selecting a checkbox on the page.  Selecting a checkbox won't trigger it?
0
Wayne Taylor (webtubbs)Commented:
No, but there are workarounds.

If they are Forms controls, you can assign a single macro to them....

Sub ControlChanged()
    ActiveSheet.Tab.ColorIndex = 3
End Sub

Open in new window


If they are ActiveX controls, they will each have their own events that will need to be handled. For example, for a control named CheckBox1, this event in the parent worksheet's code module will handle any changes to it's value...

Private Sub CheckBox1_Change()
    Me.Tab.ColorIndex = 3
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave KongAuthor Commented:
Thank you for the response.  I am unfortunately cranking today in preparation for vacation.  I may not be able to look at this further until I return on the 13th.
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.