How to stop Excel from updating a cell?

As I understand it, once B1 displays '5', you don't want it to change again, until perhaps you delete the entry in B1. If so, you will need to utilize VBA to do as you wish. This method does not fire on 'Refresh', but upon double clicking any cell.

Copy this event handler to the clipboard (highlight the entire handler, right click inside the highlighted area, and 'Copy').

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

On Error GoTo errhandler

ActiveWorkbook.RefreshAll

Application.EnableEvents = False

Range("A1").Formula = "=RANDBETWEEN(1,9)"

If Range("A1").Value = 5 Then

Range("B1").Value = 5

End If

errhandler:

Application.EnableEvents = True

Target.Offset(1).Select

End Sub

Right click the sheet tab at the bottom of your worksheet and select 'View Code'.

Paste the event handler into the white editing area to the right (right click inside the area and Paste).

Close the VBE (red button - top right).

Save the workbook as an Excel Macro-Enabled Workbook to retain this functionality in the future. Archive the original workbook to avoid confusion as to which to use.

To enter a random number between 1 and 9, double click any cell. Each double click triggers the RANDBETWEEN function in A1. Once B1 displays '5', it will not change again until you delete the entry in B1.

Note: Double clicking will initiate a 'Refresh All' action on the active workbook before processing the RANDBETWEEN function.

Advise if I have misinterpreted you goal.