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!