Macro to auto add upon selection
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
|