update all sheets on workbook activation

[Jon Henry]

Im working on a spreadsheet with about 100-150 worksheets in it. On each sheet there are two dates(one pre-determined and one the current date) that I want to compare to determine whether or not they are <4 years nine months apart, >4 years 9 months apart or >5 years apart. Each state will make the tab a certain color, green, yellow and red respectively.

Im using the worksheet_change event right now to update the color after Ive entered a date using this code:

Please Login or Register  to view this content.

My problem now is when I open the workbook, the colors wont update without a change to each sheet. I want to make it so that when I open the workbook, the colors update themselves. I can do this right now using the workbook_activate event and this code:

Please Login or Register  to view this content.

Doing this however requires I input one copy of this code for each sheet(thats a lot). Im looking for a way to do this with much less code, maybe referencing the cells a different way(ie ActiveWorkbook.something.something?

Is it possible or do you guys have any other suggestions for a more efficient way to do it?

Thanks
Jon

[broro183]

hi Jon,

Please edit your initial post by including "code tags" around your lines of code. This can be done by selecting your text & then pressing the "#" icon which appears immediately above your message.

The most efficient way I know of is to use a "for each" structure combined with a "with" statement ie:
(note, this is untested)

Please Login or Register  to view this content.

hth
Rob

[davesexcel]

try the workbook event

Please Login or Register  to view this content.

http://www.contextures.com/xlvba01.html

[Jon Henry]

Worked like a champ, thanks broro.

I was using those events Dave. The problem was using them, when I opened a workbook I had to go to each sheet to activate it and make it run the code. I needed something that ran it on all sheets on workbook open. Thanks for the help though.

[broro183]

Thanks for the feedback Jon - I'm pleased I could help :-)

Rob