Sums & Colours

[DeskFlyer]

Hi All,

have a unique request....
In the example below

Beenleigh Mall 2004WS.xls
	R	S	T	U
35		Total
36	Total	Hours	Wages
37
38	38.00	38.00	0.00
39
40	23.75	37.25	0.00
41	7.00		0.00
42	6.50		0.00
43
44	6.00	6.00	0.00
SUMMARY

I want to......in the first cell in column S underneath each grey cell, sum the hours in column R for the cells inbetween the grey cells (which serve as 'markers' for different employees).
In S38 i just want it be R38, but in S40 (the 1st white cell after the next grey cell), i want it to sum R40:R42 (ie, up to the next grey cell which marks a change in employee)

Is this possible in any way?

Thanks

DF

 

[Derek]

Hi
Crossed messages.
The macro should only run when you make a change in row R.
The event macro should be in the window that opens when you go to the VB Editor via the sheet tab. The Total macro needs to be in a standard module in the same workbook. You should be able to locate the module in the projects window of the VB Editor. Alternativelly from the worksheet, select Tools/Macro/Macros (select the macro Total)/ Edit. This should take you to the module containing the macro.

hope this makes sense to you.

 

[DeskFlyer]

Ok, well that all makes sense now, but is it possible to have it run without directly editing a cell in column R?

the cell in column J of any row uses data validation to allow the user to select one of three options which in turn changes the hours worked and this is reflected in the sum function in column R - the code for Worksheet_Change macro doesn't appear to handle this.

can that be done using the following somehow?

Private Sub Worksheet_Calculate()

End Sub

cheers
Phil

 

[Derek]

If I follow you correctly you actually want the code to run if a change is made to any cell in column J (ie column 10). In that case change the 18 in the event macro to 10.

regards
Derek