Option Explicit
Option Compare Text
Sub test()
Colorer Feuil4.[D2:E14], Feuil4.[A2:A14]
Colorer Feuil1.[D2:E14], Feuil4.[A2:A14]
Colorer Feuil2.[A2:B14], Feuil4.[A2:A14]
End Sub
Sub Colorer(ByVal RngPhra As Range, ByVal RngMots As Range)
Dim TMots() As String, TCoul() As Long, TSize(), L&, Cel As Range, Z As String, P&
ReDim TMots(1 To RngMots.Rows.Count), TCoul(1 To UBound(TMots)), TSize(1 To UBound(TMots))
For L = 1 To UBound(TMots, 1)
With RngMots(L, 1): TMots(L) = .Value: TCoul(L) = .Font.Color
TSize(L) = .Font.Size: End With: Next L
For Each Cel In RngPhra.Columns(1).Cells
Z = Cel.Value
For L = 1 To UBound(TMots)
P = InStr(Z, TMots(L))
If P > 0 Then
With Cel.Characters(Start:=P, Length:=Len(TMots(L))).Font
.FontStyle = "Bold": .Color = TCoul(L)
.Size = TSize(L): End With
Cel.Offset(, 1).Value = TCoul(L): End If: Next L, Cel
End Sub