asked on

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

Wayne Taylor (webtubbs)

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

Dave Kong

ASKER

That is elegantly simple! I will try this out as soon as I can. Thank you!

Dave Kong

ASKER

Question: Where exactly do I put this code?

Dave

Wayne Taylor (webtubbs)

Right-click on the sheet tab and select "View code". Put the code in this module.

Dave Kong

ASKER

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.

Wayne Taylor (webtubbs)

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

Dave Kong

ASKER

I see it working now! I was originally selecting a checkbox on the page. Selecting a checkbox won't trigger it?

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave Kong

ASKER

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.

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.