Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Set r = Range("A2:A" & Rows.Count) 'plage à adapter
r.NumberFormat = "yyyy-mm-dd"
Set r = Intersect(Target, r, UsedRange)
If r Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
On Error Resume Next 'si aucune SpecialCell
Columns(1).Insert 'insertion de la colonne auxiliaire
For Each r In r.Areas
r.Offset(, -1) = "=1/(RC[1]>=DATEVALUE(""1/1/2000""))/(RC[1]<DATEVALUE(""1/1/2100""))"
r.Offset(, -1).SpecialCells(xlCellTypeFormulas, 16).Offset(, 1).ClearContents
Next
Columns(1).Delete 'suppression de la colonne auxiliaire
Application.EnableEvents = True 'réactive les évènements
End Sub