I don't think you can do this with VBA code. Any formula that 'remembers'
R1C2 will not remember it once it changes.

This code will do what you want. Select the sheet and right-click on its
name tab and choose [View Code] from the list and then copy and paste this
code into the code module presented to you.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:C1")) Is Nothing Then
Exit Sub
End If
If Range("C1") = 1 Then
Range("A1") = Range("B1")
End If
End Sub

"Sandeep Himatsingka" wrote:

In R1C3, I fill manually 1 or delete this 1 (i.e. blank). Only 2 actions in
R1C3.
In R1C2, I fill,say, number 30 then overwrite 30 with say 57 then overwrite
57 with 807 & so on. R1C2 is being filled regularly manually by me with
different numbers.
I want in R1C1 same number of R1C2 as long as R1C3 contains 1. The moment I
delete 1 from R1C3 (make it blank), I want R1C1 should retain the last number
(which it should have been copying thr' an excel formula) from R1C2 & STOP
further copying same number from R1C2 unless R1C3=1.

How to do it? Pls give the formula in R1C1????
Many thanks