Autres Formule excel version 2007

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Recherche As Range, NC As Range, P As Range, Dates As Range, Complement As Range, R As Range, c As Range, Q As Range
Set Recherche = [T11:X11]
Set NC = [Y11] 'recherche du numéro complémentaire
Set P = [E:I]
Set Dates = [D:D]
Set Complement = [J:J]
If Intersect(Target, Union(Recherche, NC)) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
On Error Resume Next 'si aucune SpecialCell
Recherche.Offset(1).Resize(Rows.Count - Recherche.Row, Recherche.Columns.Count + 2).Delete xlUp 'RAZ
If NC = "" Then
    Set R = P
Else
    Complement.Replace NC, "#N/A", xlWhole
    Set R = Complement.SpecialCells(xlCellTypeConstants, 16)
    If R Is Nothing Then Exit Sub
    R = NC
    Set R = Intersect(R.EntireRow, P)
End If
For Each c In Recherche
    If c <> "" Then
        P.Replace c, "#N/A", xlWhole
        Set Q = Nothing
        Set Q = P.SpecialCells(xlCellTypeConstants, 16)
        If Q Is Nothing Then Exit Sub
        Q = c
        Set Q = Intersect(Q.EntireRow, P)
        Set R = Intersect(Q, R)
    End If
Next
'---résultat---
R.Copy Recherche(2, 1)
Intersect(R.EntireRow, Complement).Copy NC(2)
Intersect(R.EntireRow, Dates).Copy NC(2, 2)
End Sub