Change a Related Cell by the Same Amount in Excel
Question details
Automatically adjust a second cell by the same increase or decrease applied to the first cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user edits a numeric value and wants the related cell to change by the same amount.
- Observed behavior
- The related cell should increase or decrease by the difference between the first cell's old and new values.
Identify the cells to monitor and their related cells. The macro below watches column B from B2 onward and updates the cell in column C on the same row; it requires desktop Excel with VBA enabled.
Use a Worksheet_Change macro to update the adjacent cell
Use this method when both cells contain editable numeric values and the related cell should retain its current value plus the change.
The macro uses Excel's Undo command to retrieve the previous value, then reapplies the new value and adds the difference to the adjacent cell. It handles one edited cell at a time and treats a blank previous or related value as zero.
It ignores cleared cells, nonnumeric entries, formulas, and multi-cell edits. The macro also changes Excel's undo history, so prior actions may no longer be available to undo.
In desktop Excel, right-click the worksheet tab containing the cells and select View Code.
Paste this code into the worksheet's code window: Private Sub Worksheet_Change(ByVal Target As Range) Dim newValue As Double Dim oldValue As Variant Dim relatedValue As Double Dim oldWasFormula As Boolean If Target.CountLarge <> 1 Then Exit Sub If Intersect(Target, Me.Range("B2:B" & Me.Rows.Count)) Is Nothing Then Exit Sub If Target.HasFormula Or IsError(Target.Value2) Then Exit Sub If Len(CStr(Target.Value2)) = 0 Then Exit Sub If Not IsNumeric(Target.Value2) Then Exit Sub If Target.Offset(0, 1).HasFormula Or IsError(Target.Offset(0, 1).Value2) Then Exit Sub If Len(CStr(Target.Offset(0, 1).Value2)) = 0 Then relatedValue = 0 ElseIf IsNumeric(Target.Offset(0, 1).Value2) Then relatedValue = CDbl(Target.Offset(0, 1).Value2) Else Exit Sub End If newValue = CDbl(Target.Value2) On Error GoTo HandleError Application.EnableEvents = False Application.Undo oldValue = Target.Value2 oldWasFormula = Target.HasFormula Target.Value2 = newValue If oldWasFormula Or IsError(oldValue) Then GoTo InvalidOldValue If Len(CStr(oldValue)) = 0 Then oldValue = 0 ElseIf Not IsNumeric(oldValue) Then GoTo InvalidOldValue End If Target.Offset(0, 1).Value2 = relatedValue + newValue - CDbl(oldValue) GoTo CleanUp InvalidOldValue: MsgBox "The previous value must be a number or blank. The related cell was not changed." GoTo CleanUp HandleError: MsgBox "The related cell could not be updated: " & Err.Description CleanUp: Application.EnableEvents = True End Sub
Select File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm). When reopening the file, enable macros if prompted and if you trust the workbook.
Enter numeric values in B2 and C2, then change B2. For example, increasing B2 from 10 to 13 should increase C2 by 3.

Use a baseline formula for a linked cell
Use a formula instead of VBA when the related cell does not need to be edited independently.
Link Related Values with a Formula in WPS Spreadsheet
Use a fixed baseline formula in WPS Spreadsheet to make one cell follow another by the same increase or decrease, without a macro.
- 1. Open the spreadsheet: Open WPS Spreadsheet and select Open to load your workbook.
- 2. Set the baseline: Enter the starting value of B2 in E2 and the intended starting value of C2 in F2.
- 3. Link the cells: Select C2 and enter =B2+($F$2-$E$2).
- 4. Test and save: Change B2 to check that C2 moves by the same amount, then select File > Save As to save the workbook.

Frequently Asked Questions
Why does the macro disable Excel events?
It sets Application.EnableEvents to False before changing cells so its own edits do not trigger Worksheet_Change again. The cleanup section turns events back on.
Will the macro work when I paste several cells at once?
No. The code exits when Target.CountLarge is greater than one. Edit or paste into one monitored cell at a time.
What happens if I clear a monitored cell?
The macro ignores a cleared cell and leaves its related cell unchanged. It also ignores nonnumeric entries and formulas.
Can I use the VBA solution in Excel for the web?
Excel for the web does not run VBA macros. Open the workbook in desktop Excel to use the Worksheet_Change event, or use the baseline formula instead.
How do I change which cells the macro monitors?
In the worksheet's View Code window, replace the B2:B range in the Intersect line with your desired range. Change Target.Offset(0, 1) if the related cell is not immediately to the right.




