Selected Answer
Sunil
The attached revised file allows for the possibility of multiple simultaneous changes in Sheet1 column B (as you clarified in the Discussion under your question).
When you open the file (macro-enabled), values in Sheet1 column B are stored in a variable LastVals via this code (within the ThisWorkbook section of VBA):
Dim LstRw As Long, LastVals As Variant
Private Sub Workbook_Open()
With Sheet2
' capture depth of sheet (col B) and current values
LstRw = .Range("B" & Rows.Count).End(xlUp).Row
LastVals = .Range("B1:B" & LstRw).Value
End With
End Sub
In the same section, this event code is stored, which uses LastVals to look for changes. It's commented so you can follow what's happening:
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim Rw As Long
' check for changes occured on the target sheet only
If Sh.Name <> Sheet2.Name Then Exit Sub
' prevent retrigger and reverse the change
Application.EnableEvents = False
With Sheet2
LstRw = .Range("B" & Rows.Count).End(xlUp).Row
' compare each cell in B with LastVals to find change
For Rw = 2 To LstRw
' see if a value changed
If LastVals(Rw, 1) = "Buy" And .Cells(Rw, 2).Value = "Sell" Then
' if it changes to Sell, add A to D...
.Cells(Rw, 4).Value = .Cells(Rw, 4).Value + .Cells(Rw, 1).Value
ElseIf LastVals(Rw, 1) = "Sell" And .Cells(Rw, 2).Value = "Buy" Then
' if it changes to Buy, add A to C...
.Cells(Rw, 3).Value = .Cells(Rw, 3).Value + .Cells(Rw, 1).Value
Else
' assume no change and do nothing
End If
Next Rw
'save changed values
LastVals = .Range("B1:B" & LstRw).Value
Application.EnableEvents = True
End With
End Sub
If you change Sheet1, that triggers the event and Sheet2 gets updated as per your "Remarks" (=rules!).
You can add extra rows (in both sheets, with Events disabled so they both match) but take care that there aren't too many rows so the code doesn't finish before the next chnage occurs.
You can use Copy/Paste (with the opposites of column B, in column F say) to test that it works for multiple simultaneous changes in Sheet1.
Hope this fixes your problem. If so, please remmeber to mark this Answer as Selected.
