update numerical value automatically based on change in another

Question

I am working an Excel spreadsheet to update the numerical value in a cell based on the value in another cell.&nbsp; For instance, assuming cell A1 has a number 10 and cell B1 has a number 20, (1) if 10 is changed to 30 in cell A1, then 20 will be changed to 30 in cell B1 as well, (2) if 10 to changed to 5 in cell A1, then there is no change in cell B1

hansvogelaar · Answer

il38694&nbsp;
Do you want B1 to keep track of the highest value in A1? That requires a bit of VBA code.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)
    ' Change range as needed, for example to A1:A20
    Const MyRange = "A1:A1"
    Dim rng As Range
    If Not Intersect(Range(MyRange), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        On Error GoTo ExitHere
        For Each rng In Intersect(Range(MyRange), Target)
            If rng.Value &lt;&gt; "" Then
                If IsNumeric(rng.Value) Then
                    If rng.Offset(0, 1).Value = "" Or rng.Value &gt; rng.Offset(0, 1).Value Then
                        rng.Offset(0, 1).Value = rng.Value
                    End If
                End If
            End If
        Next rng
ExitHere:
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
Change the range as needed.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open it.

Forum Discussion

update numerical value automatically based on change in another

1 Reply