Mouse click in several cells to sum in other cell

[Blue_Wings]

Hello,

Its possible to do a mouse click in one or several excel cells and that cells turn on the background color into gray,(and if the cell was already clicked and if clicked again the background color desapears and the value will not be added to the sum [just like a press button]) and put the sum result in another cell?

Another thing that i wanted to do, if a value (number) of a cell is negative can that same cell value became bold and red for example?

Many thanks.

[JeanRage]

Hi,

See attached sample ...

HTH

[Blue_Wings]

Yes, thats it, but i want to do it for 2 tables that are in the sheet, how can i especify to sum just a few colums?
thanks

i want to sum vertically the colum A,B,C and in the other table the colum G,H,I

see the attach please

[JeanRage]

See attached file ... is it OK ?

[Blue_Wings]

See attached file ... is it OK ?

Yes its perfect! thanks!

I am gonna try and see if i understand the code, do you know a link on the internet so i can give my first steps in the excel code, to learn it?
what is it called? VBA ?

[JeanRage]

Glad you could fix your problem ...
Yes, the event macro is written in VBA ...
If you are interested in learning it :
http://www.xlpert.com/toc.htm

HTH

[Blue_Wings]

Sorry to bother you again, but i am trying to copy paste the code (to my original excel sheet) that you had written but it doesnt work. I am reading a small tutorial of VBA but until this point i couldnt understand how does the code work: (i supose its only this part that do all the job)

BeforeDoubleClick - easy to understand

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column > 9 Then Exit Sub ' if more than 9 colums it will not work
Dim i As Integer
i = Target.Column
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 15 'this is the gray color i think
Cells(17, i).Value = Cells(17, i).Value + Target.Value 'the cell in row 17 is the sum?
Else
Target.Interior.ColorIndex = xlNone
Cells(17, i).Value = Cells(17, i).Value - Target.Value
End If
Cancel = True
End Sub

'All the other things i cant understand

[JeanRage]

Hi,

Congratulations on your excellent understanding of the code ...

Forgot to tell you procedure to copy an event macro:
point to sheet tab name
right click ans select view code
then copy code ...

HTH

[Blue_Wings]

Its giving me an error when i do the doubleclick in the cell: Type mismatch (Error 13)
And when i do the debug it says that its on this line:

-> Cells(17, i).Value = Cells(17, i).Value + Target.Value

If Target.Column > 9' i changed the 9 to 23
Cells(17, i).Value = Cells(17, i).Value + Target.Value' changed the 17 to 29

but even without the changes it gives the same error
I had send a private message for you also
thanks

I think i discovered the problem... my table have rows with 2 cells merged per row (thats why its giving Type mismatch (Error 13)), and in your example TestGrey2 its only 2 cell per row. Its not possible with merged cells?

[JeanRage]

If I can make a recommendation : Never use merged cells ... the only thing you can be sure of ... is that they will create problems ....

To center as if you had used merged cells, use :
Format>Cells>Alignment>Horizontal>Center Across Selection ...

HTH