Enter a value in one cell and subtract from another that has a value already in it without formula

[lurch7892]

I currently has a range of cells B4:B801. There are no formulas in these cells, just values. I need to input a number in the corresponding cell in column A, hit ENTER, and have this number subtracted from the cell in column B, and display the result.

Example: B4 contains the number 28. I types in the number 5 in cell A4 and hits ENTER. B4 should change to number 23.

Can someone help with this? I'm sure it has to be done in code.


Thanks
Lurch7892

 

[Joe4]

Welcome to the Board!

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window that pops up.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("A:A"))
    
    If rng Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    For Each cell In rng
        cell.Offset(0, 1).Value = cell.Offset(0, 1).Value - cell.Value
    Next cell
    
    Application.EnableEvents = True
    
End Sub

So whenever you enter a value in column A, it will automatically subtract it from column B.