Macro to populate surrounding cells based on the value of another

Hi All,

I'm really hoping someone can help me with writing a macro for something I am trying to achieve. I have attached the document I am using and will try and explain what it is I am trying to do:

Basically, I am using Excel 2003 to plot the number of days it would take (across various weeks) to complete a 6 day project. The dates will be plotted across a row, so in the attached example, Column CK11 shows 1 day week commencing 26th Nov 2012, CL11 shows 0.75 day the following week, etc.

All projects will take 6 days, and the breakdown of the 6 days I need is as follows:

0.25 + 0.25 + 0.25 + 0.25 + 0.25 + 0.25 + 0.25 + 0.25 + 0.25 + 0.75 + 1 + 0.75 + 0.25 + 0.25 + 0.25 + 0.5. This equals 6 days and can be seen in Row 11.

The '1 day' in the above sequence needs to be plotted in the correct 'week commencing' column based on the paydate in Column E. I am pleased to say that I have created a successful formula to automatically do this.

What I now need is for someone to kindly help me by advising how I can automatically populating the surrounding cells based on the location of which cell contains the 1 day.

So in the attached example, you can see that cell CK10 automatically has 1 in there. How do I create a Macro that tells excel to populate Row 10 with the next 5 cells after the 1 day with 0.75 + 0.25 + 0.25 + 0.25 + 0.5 and the previous 10 cells before the 1 day with 0.25 + 0.25 + 0.25 + 0.25 + 0.25 + 0.25 + 0.25 + 0.25 + 0.25 + 0.75?

Hope this makes sense, if not, please feel free to ask me anything you are unsure of.

Many Thanks!

P.S. I am a newbie and this is my first post :-)