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?
 
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
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Dave KongAuthor Commented:
Question:  Where exactly do I put this code?

Dave
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.