# how to conditionally 'freeze' the value in a cell?

#### excelquest23

##### Board Regular
Hello,

I would like for the value in cell A1 to be randomly drawn between 5 and 10 if the value of A2 is not 1, and to be 'frozen' with the existing value in A1 if A2 is equal to 1.

So, if A2 has a value 2 then value in A1 could be 8.

If value of A2 is then changed to 1, I would like for value in A1 to remain 8.

Is there an easy way to do this with an IF command? I tried the following but it didn't work:
=IF(A2=1,A1,(RANDBETWEEN(5,10)))

Thanks!

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### robert phillips

##### New Member
Use the worksheet change event to track changes in A2 and update A1 with the vba rnd function.

When A2<>1, Rnd number generated & put into A1

Then When A2=1, You Can Do Nothing and A1 will not be changed
OR
To guarantee that A1 retains the same value as the last time it was updated when A2<>1
You can simply save the value of A1 each time it is changed and push it back to A1
when A2=1
OR
You can regenerate the same number by supplying the Rnd function with 0

Private Sub Worksheet_Change(ByVal Target As Range)
Static OldA1 'hold value in case you want to push oldval back to A1

On Error GoTo BeSafe

Application.EnableEvents = False 'momentarily prevent subsequent change event

If Target.Value <> 1 Then
OldA1 = Int(Rnd() * 5 + 0.5) + 5
Range("A1").value=OldA1
Else
'Do Nothing
'Range("A1").Value = Int(Rnd(0) * 5 + 0.5) + 5 'this regenerates old number to A1
'Range("A1").value=OldA1 'this pushes old value to A1
End If
End If

BeSafe:
Application.EnableEvents = True
End Sub

If you try to do this with excel IF functions, no way to return the cell value to itself because causes circular reference. You would have to have a secondary cell somewhere holding the value you want to put in A1.

Replies
13
Views
217
Replies
6
Views
252
Replies
6
Views
125
Replies
2
Views
114
Replies
2
Views
91

1,191,707
Messages
5,988,225
Members
440,139
Latest member
ngaicuong2017

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back