Copy/paste into Thisworkbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Intersect(Target, Range("B300")) Is Nothing Then Exit Sub
ActiveSheet.Tab.ColorIndex = 10
End Sub

Will work on whichever sheet is active and whenever you manually fill B300
on that sheet.


Gord Dibben MS Excel MVP



On Fri, 20 Nov 2009 09:06:03 -0800, Mike
wrote:

Its not working i pasted this in & closed it like you asked i am using cell
B300 for each sheet 1 to 52 & i want to change colour of tabs from blue to
green once B300 is filled

"Gary''s Student" wrote:

Excel needs to know when you have completed a sheet. Say we pick B9. If you
enter a value in B9 Excel will do the coloring. Enter this event macro in
the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub
ActiveSheet.Tab.ColorIndex = 10
End Sub



Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


Repeat for each worksheet.
--
Gary''s Student - gsnu200908


"Mike" wrote:

I have 52 work sheets named week 1 to week 2 once data is completed for each
week is it possible to have the tabs change colour automatically to indicate
week is finished to go on to next week if it is HOW