Automatically change tab colour

C

calin

This was a serious challenge to me: I need to have the tab colour
changed automatically if data is entered in a certain range in a
spreadsheet with many worksheets, so I do not have to go through every
worksheet every week to see if data was entered. Is this possible?

Thank you,
Calin Alexandrescu
Project Manager
 
D

Dave Peterson

You can use a workbook event:

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myAddrToCheck As String
myAddrToCheck = "A1:C9"
If Intersect(Sh.Range(myAddrToCheck), Target) Is Nothing Then
Exit Sub
Else
Sh.Tab.ColorIndex = 3
End If
End Sub


This goes behind the ThisWorkbook module.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm
 
Top