Record Value through Remote data

Hello,

I trying below concept in below coding:

Whenever in the Column C, there is any change from "Empty value" to "Buy" or from "Sell" to "Buy" than the respected quantity available in Column D to add to Column E. 

Whenever in the Column C, there is any change from "Empty value" to "Sell" or from "Buy" to "Sell" than the respected quantity available in Column D to add to Column F 

For example, If whenever there is any change in Trade Column from Blank value to Buy or from Sell to Buy (positive move), than the given quantity i.e. 100 in quantity column to add to Total Buy column and If whenever there is any change in Trade Column from Blank value to Sell or from Buy to Sell (negative move), than the given quantity i.e. 100 in quantity column to add to Total Sell column and so on.

I am trying the VBA coding based on your solution given in Record Number of sum to formula based cell - Excel , but stuck in getting the desired result.

Below is the code in Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("C:C")) Is Nothing Or Not IsNumeric(Target.Value) Then Exit Sub

Dim Rmt As Range

Set Rmt = Sheet2.Range(Target.Address)

Application.EnableEvents = False

Select Case Target.Value - Rmt.Value

    Case Is = "Buy"
        Target.Offset(0, 2).Value = Rmt.Offset(0, 1).Value
    Case Is = "Sell"
        Target.Offset(0, 3).Value = Rmt.Offset(0, 1).Value
    Case Is = 0

    Case Else

End Select

Target.Value = Rmt.Value
Target.Offset(0, 1).Value = Rmt.Offset(0, 1).Value

Application.EnableEvents = True

End Sub

Below is the code in Sheet2:

Private Sub Worksheet_Calculate()


Dim Rpt As Range, Cll As Range

Application.EnableEvents = False
Application.Calculation = xlCalculationManual

For Each Cll In Sheet2.Range("C3:C200")
    If Not IsNumeric(Cll.Value) Then

        Set Rpt = Sheet1.Range(Cll.Address)

        Select Case Cll.Value
            Case Is = "Buy"
                Rpt.Offset(0, 2).Value = Rpt.Offset(0, 2).Value + Cll.Offset(0, 1).Value
            Case Is = "Sell"
                Rpt.Offset(0, 3).Value = Rpt.Offset(0, 3).Value + Cll.Offset(0, 1).Value
            Case Is = 0

            Case Else

        End Select
        Rpt.Offset(0, -1).Value = Cll.Offset(0, -1).Value
        Rpt.Value = Cll.Value
        Rpt.Offset(0, 1).Value = Cll.Offset(0, 1).Value
    End If
 Next Cll

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic


End Sub

Attached the excel sheet.

Please guide.

Thanks,