Macro to auto add upon selection

[paulie102003]

Hello all

So, office 2013. I have a spreadsheet that I use for managing my finances. I have been trying to teach myself VBA and have had a bit of success but am a bit stuck as to my next move.

So far I have the cells highlighting when I click on them, with a "clear" button so that all the cells clear of their highlighting upon clicking it and executing the macro.

My question therefore is this, (attached is the actual worksheet, but with fake details entered for obvious reasons) I would like to create a way so that, when a line is highlighted by using the macro as it currently is, it takes the figures from the cells circled in red (D37 and G37 in this case), automatically deducts it from the total (circled in green - D48 and G48), and puts the total into D52 and G52. With each subsequent highlight, still subtracting from D37 and G37, and continually adding into D52 and G52, (circled in blue) so it gives me a running total for each highlighted line.

Ultimately, D37 subtracts from D48 and adds as a running total into D52. G column does the same thing.

So, in the example of the attached picture. When I click on "example 4", it automatically highlights the whole line at the moment but I want it to automatically take the £157, deduct it from the "total outstanding", and add it into the "saving". The direct debit amount of £22 does the same thing at the same time. If I then clicked on say "example 3", it would deduct the £9222, and add it onto the 157, both of which would display in "saving". Upon hitting clear, it resets those amounts to what they were prior to being clicked.

Does that make sense? (im not good at explaining) When I hit the clear button, it then clears the highlights (as it is already doing) but also resets the subtractions at the same time.

Im sure its quite simple, I just cant figure this bit out. The code I have at the moment is as follows


Thanks everyone in advance

*******************
Sub sbRangeFillColorExample3()

Range("B33:h46").Interior.Color = RGB(255, 255, 255)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rownumber As Integer

rownumber = ActiveCell.Row

If Application.Intersect(ActiveCell, [calculations]) Is Nothing Then

If Application.Intersect(ActiveCell, [Header]) Is Nothing Then

If ActiveCell.Value <> "" Then

Range("b" & rownumber & ":h" & rownumber).Interior.Color = RGB(255, 255, 9)

End If

End If

End If

End Sub

[NoSparks]

in D50 enter formula =SUM(D34: D46)
in D52 enter formula =D50-D48
in G50 enter formula =SUM(G34:G46)
in G52 enter formula =G50-G48

Sub sbRangeFillColorExample3()
     ' reset/clear button
     Range("B34:h46").Interior.Color = RGB(255, 255, 255)
     Range("D48").Formula = "=SUM(D34:D46)"
     Range("G48").Formula = "=SUM(G34:G46)"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'limit to single cell
    If Target.Count > 1 Then Exit Sub
    'limit to Company column
    If Intersect(Target, Range("B34:B46")) Is Nothing Then Exit Sub
    ' ok to continue
    Intersect(Target.EntireRow, Range("B33:H46")).Cells.Interior.Color = RGB(255, 255, 9)
    Range("D48") = Range("D48") - Range("D" & Target.Row)
    Range("G48") = Range("G48") - Range("G" & Target.Row)
End Sub

A picture is not a worksheet.
Posting an actual worksheet would likely get better solutions.

[Pecoflyer]

Originally Posted by NoSparks

A picture is not a worksheet.
Posting an actual worksheet would likely get better solutions.

And would make the thread easier to read

@paulie
Please wrap code with code tags. It makes the thread easier to read and the code easier to copy
To do this edit your post, click " go advanced", enter your post - select the code and click the #button
Thanks