VBA to modify cell formula

[default_name]

Hey guys,

I am trying to get some inputted cell values to change, based on values in another column.
Explanation:
There are two columns of values, a Bucket and Actual.
The bucket value represents an amount that is 'in the bucket'. Each bucket value can vary or change depending on the user input.
As the Actual value increases, the Bucket value decreases.
This can be achieved by simply using a subtraction formula in the Bucket cell.
(see below for a visual)

	A	B
1	Bucket	Actual
2	40 (=40-B2)
3	15 (=30-B3)	15
4	50 (=100-B4)	50

However, I am looking to make this workbook a little bit more intuitive so that the user does NOT need to set up the Bucket formula every time.
Is there a way (possibly using VBA?) to automatically have the Bucket values (in column A) change depending on the Actual values (in column B)?

Basically, it would be nice to just be able to type in the value of 40 into A2 and have it decrease by whatever is placed in B2 (without having to enter in the =40-B2 formula into A2)
Is something like this even possible?

Thanks in advance

 

[offthelip]

You can put this code in the worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range(Cells(1, 1), Cells(10, 1))) Is Nothing) Then
 rowno = Target.Row
 Application.EnableEvents = False
  Cells(rowno, 1) = Cells(rowno, 1) - Cells(rowno, 2)
 Application.EnableEvents = True
End If

End Sub

Note this doesn't change the value in column A if the value in column B changes, but you didn't ask for that

 

[offthelip]

An alternative is to use this code to write the equation in the cell, which takes the value which has been entered:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range(Cells(1, 1), Cells(10, 1))) Is Nothing) Then
rowno = Target.Row
Valu = Target.Value

Application.EnableEvents = False
  Cells(rowno, 1) = "=" & Valu & "-B" & rowno
Application.EnableEvents = True
End If

End Sub

This one will change if column B changes

 

[default_name]

An alternative is to use this code to write the equation in the cell, which takes the value which has been entered:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range(Cells(1, 1), Cells(10, 1))) Is Nothing) Then
rowno = Target.Row
Valu = Target.Value

Application.EnableEvents = False
  Cells(rowno, 1) = "=" & Valu & "-B" & rowno
Application.EnableEvents = True
End If

End Sub

This one will change if column B changes

This one works perfectly!
Thank you!!