sum by range

[sufin]

here it is i want to if b5=closing then c5= "sum(c2:c4)-(e2:e4)"
correspontantly through all row eg: b8=closing c8=" sum(c2:c7)-(e2:e7)"
but should not be updated on previous (c5)
i created a macro with cell reference but when i add a row in the middle of this its not updating

Private Sub worksheet_change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Value = "closing" Then Target.Offset(1, 0).Value = "opening"
Range("h2").Copy
If Target.Value = "closing" Then Target.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
If Target.Value = "closing" Then Target.Offset(1, 1).PasteSpecial Paste:=xlPasteValues
End Sub

additional information H2=sum(d2:d10000)-sum(e2:e10000)

 

[awoohaw]

how large is this worksheet?
If you are going to have large amounts of data, then I suggest not recording the data and processing it in the same sheet, or area of sheet.
The data should be one thing, and the report another.

dashboard_webinar.xlsx
	A	B	C	D	E	F	G	H
1
2
3	Data				Report
4	Date	Transaction	Amount		Date	Opening	Amount	Closing
5	2023-03-01	original	2000		2023-03-01		2000	2000
6	2023-03-04	tea	-50		2023-03-04	2000	-150	1850
7	2023-03-04	fuel	-100		2023-03-05	1850	-50	1800
8	2023-03-05	tea	-50
9
Sheet4

Cell Formulas
Range	Formula
G5:G7	G5	=SUMIF($A$5:$A6,E5,$C$5:C6)
H5:H7	H5	=N(H4)+G5
F6:F7	F6	=H5