Option Compare Text
Sub Recherche()
Dim Cel As Range, Noms As String
Noms = "Francine, Delphine, Estelle"
With Feuil1
For Each Cel In .Columns(1).SpecialCells(xlCellTypeConstants)
Cel.Resize(1, 5).Interior.ColorIndex = IIf(InStr(Noms, Cel.Value) > 0, 43, 0)
Next
End With
End Sub
Sub Demo1()
Const F = "TRANSPOSE(IF(ISNUMBER(MATCH(#,{""delphine"",""estelle"",""francine""},0)),ADDRESS(ROW(#),1,4)))"
With Cells(1).CurrentRegion
.Interior.ColorIndex = xlNone
VA = Filter(Evaluate(Replace(F, "#", .Columns(1).Address)), False, False)
If UBound(VA) > -1 Then .Range(Join(VA, ",")).Interior.ColorIndex = 36
End With
End Sub
Sub Demo2()
Const F = "TRANSPOSE(IF(ISNUMBER(MATCH(#,@,0)),ADDRESS(ROW(#),1,4)))"
With Cells(1).CurrentRegion
.Interior.ColorIndex = xlNone
VA = Filter(Evaluate(Replace(Replace(F, "#", .Columns(1).Address), "@", Cells(14).CurrentRegion.Address)), False, False)
If UBound(VA) > -1 Then .Range(Join(VA, ",")).Interior.ColorIndex = 36
End With
End Sub
Bonjour, mcj1997, le Forum,
Un essai en pièce jointe.
A bientôt
P. S. : Bonjour + bises, Papu-net