Using VBA to rename worksheets when a cell changes in first worksheet

Hello All,

I am a new member and have been dabbling with VBA for sometime now. Most of the time I can solve problems by reading the many different forums on the internet and use code that someone has posted to solve particular problems, but in my case I have not been able to find one to solve my issue.

I currently have a workbook that consists of 53 worksheets. The first is a summary and input sheet and the other 52 are for every week of the year. I'm aiming to use the summary sheet to input the date for the first day of the year. Not necessarily the first day of the year, but the first monday (start of the working year) of the year. In the other 52 worksheets, I have formulas which calculate the last date of that week (the sunday dates). More importantly I have a the following formula (="W-E " & TEXT(Summary!C3+6,"dd mmm").....the six is the number of days from the start of my working year and is different in every worksheet ie. 6, 13, 20, 27, etc)) which outputs text that tells me the date that that week ends on (ie 'W-E 15 Jan'). This formula is in the same cell in every worksheet.

Now, what I am aiming to do is have a VBA macro that renames each of the 52 worksheets when I change the cell with the start date in the very first worksheet.

I did have some code which I've been trying to put into my question, but for some reason my message just refuses to post. I will try to post my code in a second post. Basically the code I have comes from this website:

http://www.dailydoseofexcel.com/arch...sed-on-a-cell/

It works well, but only when I hard press the 'enter' button within the worksheet that I want the name to change.

Is there anyone kind enough to help me out with my code, or at least point me in the right direction as I've been scouring the web for a few days and I can't seem to make headway with my problem.

Lastly, I would like to also use VBA code to change the tab colour to a certain colour once the current date passes a particular worksheet end date, but if it hasn't to be a different tab colur.

I hope my explaining of my problem is not too wordy.

Cheers and thanks in advance.

Here is my code:

Please Login or Register  to view this content.

which is located in 'ThisWorkbook', and

Please Login or Register  to view this content.

which is located in 'Module1'