Ajouter une colonne dans un tableau qui reprend les formules

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 Then
        Application.EnableEvents = False
        If Target <> "" And Target.Offset(1) = "" Then Target.Offset(1).FormulaLocal = "=aujourdhui()"
        'ou date du jour fixe :
        'If Target <> "" And Target.Offset(1) = "" Then Target.Offset(1) = Date
        Application.EnableEvents = True
    End If
End Sub

Option Explicit
Private Sub Worksheet_Change(ByVal c As Range)
    On Error Resume Next
    If c.Row = 1 Then Rows("1:1").SpecialCells(xlCellTypeConstants, 23).Offset(1, 0).FormulaR1C1 = "=TODAY()"
    ' ou, pour une date figée
    ' If c.Row = 1 Then Rows("1:1").SpecialCells(xlCellTypeConstants, 23).Offset(1, 0) = Date
End Sub