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.