Forum Discussion
Keep number from changing after setting cell back to zero
hopr37
This can be done with few lines of VBA code which i wrote below. please see the attached workbook.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B2", Range("B" & Rows.Count).End(xlUp))) Is Nothing Then Application.EnableEvents = False If Target > 0 Then Target.Offset(, 1).Value2 = Target.Offset(, -1).Value2 - Target.Value2 End If End If Application.EnableEvents = True End Sub
Hello,
Use the manual calculation mode so that any change won't reflect until you press F9.
To turn this mode on go to Formulas (Ribbon tab) >> Calculation >> Calculation Options >> Manual.
Also, you can clear the below check box to keep the value as it is after saving and closing the workbook.
Hope that helps
Haytham
Thank you so much. However. It doesn't seem to work quit right.
If I start with 100 and subtract 10 and hit f9 the total does change.
I can then change the amount back from 10 to 0 and save it. The amount stays at 90-good.
If I THEN change the 0 back to 10, hit f9 then it SHOULD go to 80 but it doesn't. It stays at 90
If I put in 20 THEN it goes to 80.
I need the number to continually go down.
Hi,
Sorry for the late reply!