Jul 26 2021 11:01 PM
I am working an Excel spreadsheet to update the numerical value in a cell based on the value in another cell. 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
Jul 27 2021 12:29 AM
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 <> "" Then
If IsNumeric(rng.Value) Then
If rng.Offset(0, 1).Value = "" Or rng.Value > 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.