A Formula to "Remember" a Cell number

[Redrup]

I need a cell(Cell B4) to copy a number from another cell(Cell C4) and still show that number, even when the number from cell C4 is removed.
Thanks.

 

[Glory]

There might be a formula that would do that. I have no idea. But I know you can do it with VBA.

Push "Alt+F11" and then "Ctrl+R". Double-click your sheet name to the left side of the screen and paste this in.

Private Sub Worksheet_Change(ByVal Target As Range)
 
On Error GoTo Catch
 
If Not Intersect(Target, ThisWorkbook.ActiveSheet.Range("C4")) Is Nothing Then
 
    a = Split(Target, "-")
 
        If Not Target = "" And a(0) > 100 And a(1) > 100 Then
 
            ThisWorkbook.ActiveSheet.Range("B4") = Target
 
        End If
 
    End If
 
Exit Sub
 
Catch:
 
End Sub

This is really crude and will only work if you enter a phone number as "###-####" or "###-###-####". Someone else might have a better solution.