Formule matricielle

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   [A1].Select
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Set champ = Range("D4:D34,F4:F34,K4:K34,M4:M34,R4:R34,T4:T34")
    PremSem = Array("1er SemestreDD", "1er SemestreCF", "1er SemestreAM", _
       "1er SemestreFL", "1er SemestreRV", "1er SemestreYB", _
       "1er SemestreOccas")
    DeuxSem = Array("2nd SemestreDD", "2nd SemestreCF", "2nd SemestreAM", _
       "2nd SemestreFL", "2nd SemestreRV", "2nd SemestreYB", _
       "2nd SemestreOccas")
    '---
    p = Application.Match(Sh.Name, PremSem, 0)
    If Not IsError(p) And Not Intersect(champ, Target) Is Nothing Then
      temp = ""
      ligne = Target.Row
      col = Target.Column
      For Each c In [SALLES]
        témoin = False
        For Each s In PremSem
          If c = Sheets(s).Cells(ligne, col) Then témoin = True
        Next s
        If Not témoin Then temp = temp & c.Value & ","
       Next c
       Target.Validation.Delete
       Target.Validation.Add xlValidateList, Formula1:=Left(temp, Len(temp) - 1)
     End If
    '---
    p = Application.Match(Sh.Name, DeuxSem, 0)
    If Not IsError(p) And Not Intersect(champ, Target) Is Nothing Then
      temp = ""
      ligne = Target.Row
      col = Target.Column
      For Each c In [SALLES]
        témoin = False
        For Each s In DeuxSem
          If c = Sheets(s).Cells(ligne, col) Then témoin = True
        Next s
        If Not témoin Then temp = temp & c.Value & ","
       Next c
       Target.Validation.Delete
       Target.Validation.Add xlValidateList, Formula1:=Left(temp, Len(temp) - 1)
     End If
End Sub


JB