2 cells affected by the same amount

New Contributor

Hi there,

I am sending a pricing file to a client to decide what cost they would like to sell their items for. and based on that cost, their profit will be affected.

Screenshot 2022-12-02 131209.jpg

I have in one cell as 28, the cell to the right of it as 8.

I would like to set it up that when 28 changes to 27, then 8, changes to 7
so 8 will be affected by the same amount as 28.
example 2: when 28 is changed to 32, then 8 becomes 12.

2 Replies


Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Users opening the workbook should enable macros.

Remark: I hope the profits are random examples - the profit for a selling price of €45 is lower than that for a selling price of €43...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oldValue As Double
    Dim newValue As Double
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Range("B2:B" & Rows.Count), Target) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target.Value = "" Or Not IsNumeric(Target.Value) Then
        Target.Offset(0, 1).ClearContents
        newValue = Target.Value
        oldValue = Val(Target.Value)
        Target.Value = newValue
        Target.Offset(0, 1).Value = Target.Offset(0, 1).Value + newValue - oldValue
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
Amazing, exactly what I needed thank you.

The prices and profit are based on production costs, shipping, tax, transaction fees of each item (column A). Which the client is aware of

Thank you so much!