liste de validation de données dynamique

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect([champ], Target) Is Nothing Then
    Sheets("liste").[d2:d100].ClearContents
    If [A1] = "TOR" Then a = [TOR].Value Else a = [MOF].Value
    For Each c In a
      If IsError(Application.Match(c, Range(Cells(Target.Row, 2), Cells(Target.Row, 10)), 0)) Then
        Sheets("liste").[d65000].End(xlUp).Offset(1, 0) = c
      End If
    Next c
  End If
End Sub

'******************************************************************************************************************************
'******************************************************************************************************************************
'******************************************* établissement feuille prévi  *****************************************************
'******************************************************************************************************************************
'******************************************************************************************************************************

aa = 8 'aa est le numéro de la 1ere colonne des vols donc 8



Sheets("Stage").Select
'mv prend la valeur de la ligne de la 1ere cellule nom moniteur
mv = Application.WorksheetFunction _
        .Match("moniteurs", Worksheets(1).Range("A1:A100"), 0)
        mv = mv + 1
    S = Range("liststa").Rows.Count
j = Range("stage!B4") + 7
S = S * 2
ReDim Tablovol(S, j)
ReDim Tablomon(6, 1)
Tablomon = Range("listmon").Value
ligfnmon = UBound(Tablomon)
ligfnmon = ligfnmon - 1


'remplissage 1ere colonne tablovol avec les noms des stagiaires, et 6 colonnes suivantes avec monits
m = 14
For i = 2 To S Step 2
Tablovol(i, 1) = Range("A" & m).Value
o = mv
For rs = 2 To 7
Tablovol(i, rs) = Range("A" & o).Value
o = o + 1
Next rs
m = m + 1
Next i
Sheets("prévi").Select
  Columns("B:G").Select
    Selection.ColumnWidth = 3.75
      Columns("h:cl").Select
    Selection.ColumnWidth = 7.43
Range("A1:z" & S) = Tablovol
'remplissage liste de validation cellules listmon
For i = aa To j
For o = 2 To S Step 2
Cells(o, i).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=listmon"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    
    'MFC sur cellule moniteur selon type de vol choisi
    Strcol = IIf((i - 1) \ 26 > 0, Chr((i - 1) \ 26 + 64), "")
    Strcol = Strcol & Chr(i - ((i - 1) \ 26) * 26 + 64)
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=CHERCHE(""s"";" & Strcol & o + 1 & ")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
       .ThemeColor = xlThemeColorLight2
       .TintAndShade = 0.599963377788629
    End With
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=CHERCHE(""g"";" & Strcol & o + 1 & ")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
     Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=CHERCHE(""acc"";" & Strcol & o + 1 & ")"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
Next o
Next i

'déverouillage plage de cellules tableau prévi vol

Range(Chr(64 + aa) & 2 & ":" & Strcol & S + 1).Select
 Selection.Locked = False
'remplissage liste de validation saisie du vol
Sheets("Prévi").Select
Range("A1") = "=Stage!B5" 'rappel du stage choisi"
For i = aa To j ' j correspond au nombre total de jour du stage
For o = 3 To S + 1 Step 2  'S correspond au nombre de lignes du tableau ou au nombre de stagiaires(selon l'usage que je dois en faire)
Cells(o, i).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:= _
        "=if($A$1=""moniteur"",moniteur,IF($A$1=""PO"",PO,IF($A$1=""FI"",FI,IF($A$1=""observateur"",observateur,sélection))))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
Next o
Next i