Formule conditionnelle SI est > ou <>

Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range, i&, r As Range, mini&
If Intersect(Target, [S5]) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
On Error Resume Next 'si aucune SpecialCell
[S5] = Val(CStr([S5])) 'au cas où...
Set P = [A6:R600] 'plage adaptable
P.Columns(7).Resize(, 12) = "" 'RAZ
For i = 1 To P.Rows.Count
  If P(i, 2) <> "" Then
    Range("G2:R2") = "=SEARCH(""" & P(i, 1) & """,G1)" 'analyse des disponibilités
    '---M.Armant (on commence par lui car c'est le moins disponible)---
    If P(i, 5) <> "" Then
      Set r = Nothing
      Set r = Range("G2,N2").SpecialCells(xlCellTypeFormulas, 1)
      If Not r Is Nothing Then
        mini = Application.Min(Intersect(r.EntireColumn, Rows(3))) 'minimum en ligne 3
        For Each r In r
          If r(2) = mini Then r(i + 4) = P(i, 5): Exit For
        Next r
      End If
    End If
  End If
    '---M.Yuste---
    If P(i, 3) <> "" Then
      If P(i, 14) = P(i, 3) Then Range("M2") = "" 'neutralise la colonne M si doublon
      Set r = Nothing
      Set r = Range("H2:M2").SpecialCells(xlCellTypeFormulas, 1)
      If Not r Is Nothing Then
        mini = Application.Min(Intersect(r.EntireColumn, Rows(3))) 'minimum en ligne 3
        For Each r In r
          If r(2) = mini Then r(i + 4) = P(i, 3): Exit For
        Next r
      End If
    End If
    '---Mme Querat---
    If P(i, 4) <> "" Then
      If P(i, 13) = P(i, 4) Or P(i, 14) = P(i, 4) Then Range("O2") = "" 'neutralise la colonne O si doublon
      Set r = Nothing
      Set r = Range("O2:R2").SpecialCells(xlCellTypeFormulas, 1)
      If Not r Is Nothing Then
        mini = Application.Min(Intersect(r.EntireColumn, Rows(3))) 'minimum en ligne 3
        For Each r In r
          If r(2) = mini Then r(i + 4) = P(i, 4): Exit For
        Next r
      End If
    End If
Next i
'[G2:R2] = "" 'facultatif, mettre en commentaire pour voir les formules
Application.EnableEvents = True 'réactive les évènements
End Sub

Sub FilterColonneB()
Dim i As Variant, mes$
[A5:R600].AutoFilter Field:=2, Criteria1:="<>"
i = Application.Match("X", [B:B], 0)
If IsNumeric(i) Then ActiveWindow.ScrollRow = i
For i = 6 To 600 'limites adaptables
  If Cells(i, 2) <> "" Then
    If Cells(i, 3) <> "" Then If Application.CountIf(Cells(i, 8).Resize(, 6), Cells(i, 3)) = 0 Then mes = mes & vbLf & Cells(i, 3)
    If Cells(i, 4) <> "" Then If Application.CountIf(Cells(i, 15).Resize(, 4), Cells(i, 4)) = 0 Then mes = mes & vbLf & Cells(i, 4)
    If Cells(i, 5) <> "" Then If Cells(i, 7) <> Cells(i, 5) And Cells(i, 14) <> Cells(i, 5) Then mes = mes & vbLf & Cells(i, 5)
  End If
Next
If mes = "" Then Exit Sub
With UserForm2
  .TextBox1 = "ATTENTION !!! Les élèves suivants n'ont pas d'intervenant à cause de leur EDT :" & mes
  .Show 0 'non modal
  .TextBox1.SetFocus
  .TextBox1.SelStart = 0: .TextBox1.SelLength = 0
End With
End Sub

Sub Filtrer_ColonneB()
Dim i As Variant, n%, x$, y$, z$, mes$
[A5:R600].AutoFilter
[A5:R600].AutoFilter Field:=2, Criteria1:="<>"
i = Application.Match("X", [B:B], 0)
If IsNumeric(i) Then ActiveWindow.ScrollRow = i
n = [MAX(LEN(C6:E600))] + 1 'nombre maximum de caractères
For i = 6 To 600 'limites adaptables
  If Cells(i, 2) <> "" Then
    x = Cells(i, 3): y = Cells(i, 4): z = Cells(i, 5)
    If x <> "" Then If Application.CountIf(Cells(i, 8).Resize(, 6), x) = 0 Then mes = mes & vbLf & x & String(n - Len(x), " ") & "(Mr Yuste)"
    If y <> "" Then If Application.CountIf(Cells(i, 15).Resize(, 4), y) = 0 Then mes = mes & vbLf & y & String(n - Len(y), " ") & "(Mme Querat)"
    If z <> "" Then If Cells(i, 7) <> z And Cells(i, 14) <> z Then mes = mes & vbLf & z & String(n - Len(z), " ") & "(Mr Armant)"
  End If
Next
If mes = "" Then Exit Sub
With UserForm2
  .TextBox1 = "ATTENTION !!! Les élèves suivants n'ont pas d'intervenant à cause de leur EDT :" & mes
  .Show 0 'non modal
  .TextBox1.SetFocus
  .TextBox1.SelStart = 0
  .CMD_OK.SetFocus
End With
End Sub

If FilterMode Then ShowAllData 'si la feuille est filtrée