Put the following code in the events of your sheet
Code:Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B2:F2")) Is Nothing Then If Target.Count > 1 Then Exit Sub If Target.Value = "" Then Exit Sub Range("A1").Value = Range("A1").Value - Target.Value End If End Sub
SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.
work well BUT
can we exempt the c,e columns ?? from the calculations?
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("[COLOR=#0000ff]B2,D2,F2[/COLOR]")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Range("A1").Value = Range("A1").Value - Target.Value
End If
End Sub
Use this
Code:Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("[COLOR=#0000ff]B2,D2,F2[/COLOR]")) Is Nothing Then If Target.Count > 1 Then Exit Sub If Target.Value = "" Then Exit Sub Range("A1").Value = Range("A1").Value - Target.Value End If End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("[COLOR=#0000ff]B2:B100,D2:D100,F2:F100[/COLOR]")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Range("A1").Value = Range("A1").Value - Target.Value
End If
End Sub
Dear Sir
Thank you it worked fine
Now i'm wondering can we do the same using formula??
as you know the VBA won't work on android
Thank you
i think the formula will work in android
Can we do the same actions with formula instead of VBA code?
[COLOR=#333333]Range("A1").Value = Range("A1").Value - Target.Value[/COLOR]
hi
would you please give me a VBA for the following function
i want it to watch the column B and when i enter a value in B it multiply it by 20 and add the value to C same raw
also monitor D and the same update value to E then F and add to G
Thank you