I need a Macro...Please Help.!

[GUS]

<font=Comic Sans MS>:::Please forget me for the topic, I didn't mean to sound demanding or something similar. I am asking you for your generous contribution and please accept my truly apologies if I upset you for my careless words.:::

=)
As you can see in the above graphic of my spreadsheet. I have to insert a TOTAL WEEK 1, TOTAL WEEK 2,etc. under every other SUNDAY for every month with the respectives formulas. Which SUM(from monday to sunday). And I realy spend to much time cuting and pasting and checking that all the SUM(weedays) are correct, due to the fact that all months are not the same or start on the same weekday.
I am starting with excel and my experience with macros at this time is very poor and limited. I hope, you can help me.

I am sorry for my bad grammar and/or spelling.

Best regards,

GUS</font>
This message was edited by GUS on 2002-03-14 22:15

 

[Steve Hartman]

Hi Gus.

Thanks for a stimulating formula exercise this morning . And yes, if I understand what you want to do, this can be accomplished with formulas and conditional formatting; no VBA is needed. These are fairly complex formulas and I will explain the best I can, but if you will e-mail me I'll send the sheet I created in solving this problem.

Here goes:

In cell A2 put =IF(LEFT(B2,4)="Week","Total",WEEKDAY(B2))
and copy and paste down the column.

In cell B2 put the date of the first of any month in your favorite date format.

In cell B3 put =IF(LEFT(B2,4)="Week",B1+1,IF(WEEKDAY(B2)=1,"Week 1",B2+1)) and copy & paste down through B9.

In B10 put =IF(LEFT(B9,4)="Week",B8+1,IF(WEEKDAY(B9)=1,"Week "&RIGHT(OFFSET(B10,-8,0),1)+1,B9+1)) and copy & paste down the column.

Your quantity numbers go in column C just like your example.

In D2 put =IF(A2="Total",SUM(INDIRECT("C2:C"&CELL("row",D2)-1)),"") and copy & paste through d9

In D10 put =IF(A10="Total",SUM(C3:C9),"") and copy and paste down the column.

I've moved the hours over to column E.

Now for the conditional formatting:

Click cell A2 and click FORMAT > CONDITIONAL FORMATTING. for Condition 1 choose Cell Value Is equal to "Total" and click format to set the background color to light gray and fon to bold. Click OK and then ADD>>. Set Condition 2 to Formula Is =OR(A2=1,A2=7) and format the background color to yellow. click Ok twice. Now Copy and Paste Special > formats down column A.

Click on B2 and open the conditional formatting box. Condition 1 is Formula Is =$A2="Total". Format background light gray and font bold. Click ok twice. Copy and Paste Special > formats down Column B, C and D.

You are now ready. enter any first of the month date in B2 and watch as your colors and total lines automaticly adjust.

 

[GUS]

Thank you very much Sir,
You got mail.

I will try the above now..

GUS