Record value for sum calculation

Sunil

Thanks for adding detail to your question but I think you got the column references wrong..

To implement that approach, your mistakes were to look in the quantity column (but you explained that it was changes in rates which should trigger the updates) and not to change the If tests to check those rates.

In the attached .xlsm file, I've modified the module code (with comments and some but not all changes in bold):

Sub ChkRates()

    Application.EnableEvents = False

    Dim Rpt As Range
    Dim Cll As Range

    ' look at the rates not quantities
    For Each Cll In Sheet2.Range("D3:D25") ' was ("C3:C25")
        Set Rpt = Sheet1.Range(Cll.Address)

    ' see if rate changed
       If IsNumeric(Cll.Value) And Cll.Value <> Rpt.Value Then ' 0 Then _
'            And Cll.Offset(0, 1).Value <> Rpt.Offset(0, 1).Value _
'            And Cll.Offset(0, 1).Value <> "" Then

            ' see if rate increased
            If Cll.Value > Rpt.Value Then 'Offset(0, 1).Value > 0 Then
                    Rpt.Offset(0, 1).Value = Rpt.Offset(0, 1).Value + Cll.Offset(0, -1).Value
                ElseIf Cll.Value < Rpt.Value Then
                    Rpt.Offset(0, 2).Value = Rpt.Offset(0, 2).Value + Cll.Offset(0, -1).Value
            End If

            ' write new values
            Rpt.Offset(0, 3).Value = Now
            Rpt.Offset(0, -1).Value = Cll.Offset(0, -1).Value
            Rpt.Value = Cll.Value
            ' highlight row
            With Rpt.Offset(0, -2).Resize(1, 6)
                 .Interior.Color = vbGreen
            End With

            Else:
            With Rpt.Offset(0, -2).Resize(1, 6)
                 .Interior.Color = vbWhite
            End With
        End If
    Next Cll


    Application.EnableEvents = True
End Sub

and the Sheet2 code to:

Private Sub Worksheet_Calculate()
    Call ChkRates
End Sub

I also renamed your "remote" file to match the VLOOKUPs in Sheet2 (though you may need to alter them).

Now if you launch both files and change a (quantity followed by a) rate in the remote  .xlsx file, the sheet1 values should be added and highlighted correctly.

Hope this fixes your problem. If so, please mark this Answer as Selected.