[RESOLVED] Automate Some Cell Formatting in Excel 2002

[MartinLiss]

I have a spreadsheet that contains two identical sheets with headings like this


Row 2 is formatted as Custom 'dd' and cell C2 has the formula '1+B2'. Cell D2 has the formula '1+C2', etcetera out to cell AQ2 which has the formula 1+AP2 and so back on December 20 I put '12/20/2011' in cell B2 which generated the values in the rest of the row.

Cell B3 has the formula =LEFT(TEXT(B2,"ddd"),1), and C3 has the formula =LEFT(TEXT(C2,"ddd"),1), etcetera so the letters for the days are automatically generated when I put the current date in cell B2.

I normally generate new sheets when the days reach the end of the chart so on the 31st of January I should have done the following to produce new sheets. (I'm a few days behind).

1. Select the merged cell that contains 'Dec-11' and Format|Cells|Alignment and uncheck 'Merge cells'. Do the same for the merged cell that contains 'Jan-12' and if there had been a 3rd merged cell as there sometimes is, do the same for it.
2. Remove the dates that remain in row 1 from the 2 (or 3) cells.
3. The cells that separated the months (N1, N2 and N3) are formatted so that their left side has a dark border and I would remove that by selecting, say, cells P1, 2 and 3, and Copy|Paste Special|Formats to N1, 2 and 3. I would do the same for the second set of month separating cells if there was one.
4. I would then put the current date (or in this case since I'm behind, 1/31/2011) in cell B2 to generate the new day numbers and letters resulting in this.
   
5. January is a one-column (column B) month this time so let's ignore it for a minute. I would then select cells C1:AD1 that represent February and Format|Cells|Alignment and check 'Merge cells' and enter '02/2012' which would be changed to 'Feb-12' because the row is formatted as Date of type 'Mar-01'. I'd do the same for cells AE1:AQ1 and enter '03/2012'. Since cell B1 which represents January is too small to hold 'Jan-11' I would just enter a 'J'.
6. Then I would select cells C1:C3 and select the 'Left border' icon from the 'Borders' menu and do the same for AE1:AE3 giving me this
   
7. Finally I would copy rows 1, 2 and 3 from Sheet1 to Sheet2.

I'd be very grateful if someone could show me the code for a button (at, say, column C, row 40 on sheet1) that would request the starting date (default of current date), put that date in cell B2 and then do steps 1 through 7.