Sum macro

[Turkish04]

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?

[walruseggman]

Can you post an example data set that also shows your formatting, and the expected output of the code?

[mehmetcik]

Right click on the sheet name at the bottom of excel and select view code

Paste this code in the macro module that opens and close it.

Please Login or Register  to view this content.

The macro will work if you right click on a multiple cell range.

Right click on a single cell to view the normal right click menu.

[Turkish04]

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!!!

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Turkish04]

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.

[JOHN H. DAVIS]

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.

[Turkish04]

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.

[JOHN H. DAVIS]

Try this one. Hope it helps.

[Turkish04]

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.

[JOHN H. DAVIS]

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

[Turkish04]

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!

[JOHN H. DAVIS]

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.

[Turkish04]

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?

[JOHN H. DAVIS]

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

Please Login or Register  to view this content.

This will produce the Ambiguous Name error.

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

Please Login or Register  to view this content.