Automatically change tab colour
 

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


--
calin
------------------------------------------------------------------------
calin's Profile: http://www.excelforum.com/member.php...o&userid=29265
View this thread: http://www.excelforum.com/showthread...hreadid=526731

Automatically change tab colour
 

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

calin wrote:

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

--
calin
------------------------------------------------------------------------
calin's Profile: http://www.excelforum.com/member.php...o&userid=29265
View this thread: http://www.excelforum.com/showthread...hreadid=526731

--

Dave Peterson