XL 2016 Rendre cellule dynamique statique

Private Sub Worksheet_Change(ByVal Target As Range)
Dim F As Worksheet, r As Range
Set F = Feuil2 'CodeName, à adapter
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
'---colonne C---
Set r = Intersect(Target, Range("C2:C" & Rows.Count), UsedRange)
If Not r Is Nothing Then
    For Each r In r 'si entrées/effacements multiples
        r = CStr(F.Range("A" & r.Row - 1))
    Next
End If
'---colonne D---
Set r = Intersect(Target, Range("D2:D" & Rows.Count), UsedRange)
If Not r Is Nothing Then
    For Each r In r 'si entrées/effacements multiples
        If CStr(r) <> "" And IsNumeric(CStr(r(1, 0))) Then...