Sum macro

Hi

I have a row of cells that I will select and change the colour of the cell and in a hidden row underneath I have values for each cell.

Is it possible for a macro to calculate the total just by highlighting the cells or by having the cells highlighted and pressing a button to do the calculation.

If that is possible, how would I go about doing it please?

Thanks mehmetcik,

This is very close to what I am looking for but I would like to highlight the blank cells and it calculate from the hidden cells in the row below and post the result in the blank cells which have been highlighted.

If this can be done then it would be great!!!

Maybe:

Sub Turkish04()
Dim x As Range, y As Long, z As Long
y = 0
z = Cells(ActiveCell.row + 1, Columns.Count).End(xlToLeft).Column
For Each x In Range(Cells(ActiveCell.row, 1), Cells(ActiveCell.row, z))
    If x.Interior.ColorIndex = 6 Then
        y = y + x.Offset(1)
        x.Value = y
    End If
Next x
End Sub

Maybe:

Sub Turkish04()
Dim x As Range, y As Long, z As Long
y = 0
z = Cells(ActiveCell.row + 1, Columns.Count).End(xlToLeft).Column
For Each x In Range(Cells(ActiveCell.row, 1), Cells(ActiveCell.row, z))
    If x.Interior.ColorIndex = 6 Then
        y = y + x.Offset(1)
        x.Value = y
    End If
Next x
End Sub

Hi John H Davis,

I can't get that to work at all I'm afraid.

Basically if i'm selecting i.e. T6:AL6 I would like it to return the total of the values in T7:AL7 and place it in AL6. The right click function would be great as it is easy to perform for the user.

Try the Test Sheet. Highlight the cells you want too Sum and let me know if it does what you request with double right click. Row 3 is the target row and Row 4 is hidden.

It is nearly there again.

I need it to add all the values together and place the value in the last box only. It is currency it is adding and it only appears to round numbers off so that is not ideal.

Many thanks for your help so far.

Try this one. Hope it helps.

We are nearly there now.

The only issue I now have is that it is it is not counting the "pence". for instance £15.18 x 4 is £60.72 but it is returning £60.00. Likewise in another row £17.21 x 5 is £86.05 but it is returning £85.00.

Any help sorting that and I will be extremely grateful.

Change made - Note Dimmed y as Currency instead of long.

That is perfect! Thank you.

One last question.

If I had a second row of prices underneath how could I combine the two differences depending on colour. If I copy the code and change the offsets and colour ref it has a Ambiguous name conflict. I know I could have double click but I would prefer it to be one simple method determined on colour.

Any help with this final piece and I will be ecstatic!

You're welcome. Glad to help out. If you copy the code and paste in a module to avoid the Ambiguous name conflict you need too change the name of the Sub procedure. You can't have two Sub procedures with the same name.

Hi John H Davis,

I have tried this but to no avail.

Any chance you could post an example so I can see where I am going wrong please?

You can't have a sub named macro1 twice in your modules. Or any duplicate names for Subs.

Sub Macro1()
Do Something
End Sub

Sub Macro1()
Do Something Else
End Sub

This will produce the Ambiguous Name error.

You'd have too change it like this or another way.

Sub Macro1()
Do Something
End Sub

Sub Macro1A()
Do Something Else
End Sub