Freeze =Today() formula once data has been entered

[Jyggalag]

Hi all,

I am currently using this formula:

With its neighbor formula in column C going like this:

My question is whether it would be possible to somehow freeze my data in cell B3, C3 etc. going forward? So once the formula detects that a data entry has been in the cell 2 rows below it, it will update the date and then freeze this date.

Is this possible? It would assist me a lot. Thank you!

Kind regards,
Jyggalag

 

[MARK858]

The VBA isn't that complicated if all you are doing is inserting the date in row 3 when you amend the cell in row 5, as long as you are inserting the data in row 5 manually.

 

[MARK858]

Try the code below and see if it does what you want.
The code must go in the worksheet module (Right click the sheet tab, click View code and paste the code in the window that appears).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 5 And Target.Cells.CountLarge = 1 Then
        Application.EnableEvents = False
        If Target.Value <> "" Then
            Target.Offset(-2) = Date
            Columns(Target.Column).AutoFit
        End If
        Application.EnableEvents = True
    End If
End Sub

As stated this code is for if you changing the data in row 5 manually, if row 5 is changed via formula then it is a whole different ball game.

 

[Jyggalag]

Try the code below and see if it does what you want.
The code must go in the worksheet module (Right click the sheet tab, click View code and paste the code in the window that appears).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 5 And Target.Cells.CountLarge = 1 Then
        Application.EnableEvents = False
        If Target.Value <> "" Then
            Target.Offset(-2) = Date
            Columns(Target.Column).AutoFit
        End If
        Application.EnableEvents = True
    End If
End Sub

As stated this code is for if you changing the data in row 5 manually, if row 5 is changed via formula then it is a whole different ball game.

Thank you Mark!

I believe this solves it I will utilize this code soon

 

[MARK858]

You're welcome