Private Sub Worksheet_Calculate()
Dim n&, i&, r As Range, x$, P As Range, ncol%, tablo, lig&, col%
n = 100 'nombre de cellules cibles, à adapter
Application.EnableEvents = False
On Error Resume Next
For i = 1 To n
Set r = Nothing
Set r = Evaluate("Cible" & i)
If Not r Is Nothing Then
x = "µµµ"
x = CStr(Evaluate(ThisWorkbook.Names("TexteCible" & i).RefersTo))
Set P = r.Parent.UsedRange
ncol = P.Columns.Count
tablo = P.Value2 'matrice, plus rapide, Value2 nécessaire si date
For lig = 1 To P.Rows.Count
For col = 1 To ncol
If CStr(tablo(lig, col)) = x Then P(lig, col).Name = "Cible" & i: GoTo 1
Next col, lig
2 ThisWorkbook.Names.Add "TexteCible" & i, r.Value2
If IsError(Evaluate(ThisWorkbook.Names("TexteCible" & i).RefersTo)) Then r = "TexteCible" & i & " ?": GoTo 2
End If
1 Next
Application.EnableEvents = True
End Sub