Вот ,как вариант

Всё работает, нужно включить содержимое и макросы разрешить,
чтобы отрабатывало событие листа

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Application.Intersect(Target, Range("Test"))
If Not rng Is Nothing Then
If Target > Range("EverMax") Then Range("EverMax") = Target
End If
End Sub

Test - проверяемая ячейка, EverMax - ячеейка с максимальным значением

Ну, изменения на самом деле минимальные. Теперь код события выглядит вот так

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Application.Intersect(Target, Range("Test"))
If Not rng Is Nothing And IsNumeric(Target) Then
If Target > Target.Offset(, 1) Then Target.Offset(, 1) = Target
End If
End Sub

Теперь Test не ячейка, а диапазон. Событие слушает вводимые значения на лист Excel, и если было введено число в диапазон Test,
то проверяет соседнюю ячейку Target.Offset(, 1). Если введённое значение больше, то сохраняет его.

То есть правильнее сказать не сохраняет, а присваивает