Mfc vers texte

=SOMMEPROD((F1<>"")*ESTNUM(TROUVE(";"&$H$1:$L$1&";";";"&F1&";")))=NB($H$1:$L$1)

=SOMMEPROD((F1<>"")*ESTNUM(TROUVE(";"&$H$1:$L$1&";";";"&F1&";")))/NB($H$1:$L$1)>=1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim saisie As Range, cel As Range, t$, l%, i%, j%
Set saisie = [H1:L1] 'plage à adapter
If Intersect(Target, saisie) Is Nothing Then Exit Sub
For Each cel In [F1:G11] 'plage à adapter
  cel.Font.ColorIndex = xlAutomatic 'RAZ
  t = cel.Text
  l = Len(cel)
  For i = 1 To l
    If IsNumeric(Mid(t, i, 1)) Then
      For j = i To l
        If Not IsNumeric(Mid(t, j, 1)) Then Exit For
      Next
      If Application.CountIf(saisie, Mid(t, i, j - i)) Then _
        cel.Characters(i, j - i).Font.Color = [N1].Interior.Color 'rouge
      i = j
    End If
  Next
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim saisie As Range, r As Range, Nsaisie As Byte, t$, l%, i%, j%, n%
Set saisie = [H1:L1] 'plage à adapter
If Intersect(Target, saisie) Is Nothing Then Exit Sub
Set r = [F1:G11] 'plage à adapter
Nsaisie = Application.Count(saisie)
Application.ScreenUpdating = False
r.Interior.Color = [M1].Interior.Color 'RAZ couleur fond
r.Font.ColorIndex = xlAutomatic 'RAZ couleur police
For Each r In r
  n = 0
  t = r.Text
  l = Len(r)
  For i = 1 To l
    If IsNumeric(Mid(t, i, 1)) Then
      For j = i To l
        If Not IsNumeric(Mid(t, j, 1)) Then Exit For
      Next
      If Application.CountIf(saisie, Mid(t, i, j - i)) Then
        r.Characters(i, j - i).Font.Color = [O1].Interior.Color 'rouge
        n = n + 1
      End If
      i = j
    End If
  Next
  If n And n >= Nsaisie Then r.Interior.Color = [N1].Interior.Color 'jaune
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim saisie As Range, r As Range, tablo(), t$, l%, i%, j%, n
Set saisie = [H1:L1] 'plage à adapter
Set r = [F1:G11] 'plage à adapter
If Intersect(Target, Union(r, saisie)) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
r.Interior.Color = [M1].Interior.Color 'RAZ couleur fond
r.Font.ColorIndex = xlAutomatic 'RAZ couleur police
If Application.Count(saisie) = 0 Then Exit Sub
ReDim tablo(1 To saisie.Count)
For Each r In r
  If r <> "" Then
    For i = 1 To UBound(tablo)
      tablo(i) = saisie(i) 'initialisation
    Next
    t = r.Text
    l = Len(r)
    For i = 1 To l
      If IsNumeric(Mid(t, i, 1)) Then
        For j = i To l
          If Not IsNumeric(Mid(t, j, 1)) Then Exit For
        Next
        n = Application.Match(Val(Mid(t, i, j - i)), tablo, 0)
        If IsNumeric(n) Then
          r.Characters(i, j - i).Font.Color = [O1].Interior.Color 'police rouge
          tablo(n) = "" 'chaque valeur ne sert qu'une fois
        End If
        i = j
      End If
    Next
    If Application.Count(tablo) = 0 Then 'si toutes les valeurs ont été utilisées
      r.Interior.Color = [N1].Interior.Color 'fond jaune
    End If
  End If
Next
End Sub