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 there

You might like to try something like this code in a standard module,
(It assumes your grey rows are colorindex 15)

Sub Total()
For Each cell In Range("S1", Range("R65536").End(xlUp).Offset(0, 1))
On Error Resume Next
If cell.Offset(-1, 0).Interior.ColorIndex = 15 Then
Count = 0
x = 0
Do Until x = 20
If cell.Offset(x, -1).Interior.ColorIndex = 15 Then GoTo Skip
Count = Count + cell.Offset(x, -1).Value
x = x + 1
Loop
Skip:
cell.Value = Count
End If
Next
End Sub


regards
Derek

 

[Derek]

Hi again

Incidentally, if you select your grey rows you can use this to make them colorindex 15:

Sub Grey()
Selection.Interior.ColorIndex = 15
End Sub

Also if there are likely to be more than 20 rows between grey rows you will need to change the loop code where it says "Do until x = 20" to the max number of rows between your grey rows.

regards
Derek

 

[DeskFlyer]

That's mint! (if u still say that in the west )

I've got it to work ok by placing that code in the general section of the SUMMARY worksheet in the Visual Basic editor.

I wondered though (allow me a little poindexter fix here) if you can make it stop when it encounters another orange cell as there is a row that is orange at the bottom of each grid?

I can't seem to find a colour index list but excel is telling me that the colour is Light Orange.

Lastly - rather than making the macro run manually - is it possible to make it run whenever any of the values in column R change?

Thanks again

DF

 

[Derek]

Hi again

To find the colorindex numbers run this code on a blank sheet:

Sub Colors()
For Each cell In [a1:a56]
cell.Interior.ColorIndex = cell.Row
cell.Value = cell.Interior.ColorIndex
Next
End Sub

I have assumed the orange you used to be colorindex 46, in which case I have added a new line to the code:

Sub Total()
For Each cell In Range("S1", Range("R65536").End(xlUp).Offset(0, 1))
On Error Resume Next
If cell.Interior.ColorIndex = 46 Then Exit Sub
If cell.Offset(-1, 0).Interior.ColorIndex = 15 Then
Count = 0
x = 0
Do Until x = 20
If cell.Offset(x, -1).Interior.ColorIndex = 15 Then GoTo Skip
Count = Count + cell.Offset(x, -1).Value
x = x + 1
Loop
Skip:
cell.Value = Count
End If
Next
End Sub

This macro will work provided your columns remain as R and S. If not, you might be interested in this variation (still stops at orange row). You need to select the column first before running it - it totals the column to its left.

'INCREASE "Do until x = 20" TO MAX ROWS BETWEEN GREY ROWS
'SELECT ROW FOR TOTALS FIRST
Sub TotalSelectedColumn()
Dim col As Integer
col = Selection.Column
For Each cell In Range(Cells(1, col), Cells(65536, col - 1).End(xlUp).Offset(0, 1))
On Error Resume Next
If cell.Interior.ColorIndex = 46 Then Exit Sub
If cell.Offset(-1, 0).Interior.ColorIndex = 15 Then
Count = 0
x = 0
Do Until x = 20
If cell.Offset(x, -1).Interior.ColorIndex = 15 Then GoTo Skip
Count = Count + cell.Offset(x, -1).Value
x = x + 1
Loop
Skip:
cell.Value = Count
End If
Next
End Sub


But returning to your original code, you need an event macro to get it to run whenever there is a change in column R. Copy the following code, rightclick your sheet tab, leftclick View Code and paste it in the white area of the VB Editor. When a change is made in column R then code "Totals" will run.

regards
Derek

 

[DeskFlyer]

Hi Derek

and thankyou again - the colour index is 45.

As for the event macro - was there more code to follow?
I pasted the first example (where it's always columns R & S) in exactly the same spot as the first example you gave me and it seems that i still need to run the macro manually.

Cheers
Phil

 

[Derek]

Hi Phil

Sorry, I forgot to post it. For your orange row you need to change my colorindex code from 46 to 45 then.

Here is the event macro code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 18 Then Exit Sub
Application.Run "Total"
End Sub

regards
Derek

 

[DeskFlyer]

Cool, thankyou but I am getting an error message saying the macro 'Total' cannot be found.

The Total macro lives in the same place still - it is just not getting picked up for some reason.

Any suggestions?

Ta
Phil

 

[DeskFlyer]

Ooops, sorry - have also just noticed that if I change the values in the other cells that R is the sum of - it doesn't trigger anything, but if i type directly in the R cell, it does attempt to run the 'Total' macro.

I also double checked that both macros were in the same place by right clicking on the sheet tab & then choosing view code. They're both in the window that then pops up.

Thanks
Phil

 

[Derek]

Hi Phil

Check that the title of the macro "Total" and its reference in the event macro are identical. Otherwise I cannot think why it cannot locate it. Works here for me.

regards
Derek