New Contributor

# 2 cells affected by the same amount

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 Replies

# Re: 2 cells affected by the same amount

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``````

# Re: 2 cells affected by the same amount

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!