2 cells affected by the same amount

[victoriaroose1821]

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.

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]

@victoriaroose1821 

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
    Else
        newValue = Target.Value
        Application.Undo
        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

[#3]

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!