Forum Discussion
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.
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
- victoriaroose1821Copper ContributorAmazing, 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!