I've found a solution already but the code would be too long. Then i decided to search a way of inserting all the words I want to find and highlight into a single Find Method. I came across some ideas of using Arrays to do so and used these 3 codes to write mine (this, this, and this) but I am a new user of VBA so my final code have a problem, it highlights just the last String of the Array. I think the problem is logic but I don't know the basics of VBA so I have no idea how to correct it.
My actual code:
Sub Sample()
Dim fnd As String
Dim MyAr
Dim i As Long
Dim rng As Range, FoundCell As Range, LastCell As Range, myRange As Range
Set myRange = ActiveSheet.UsedRange
Set LastCell = myRange.Cells(myRange.Cells.Count)
fnd = "hugo/vw/osnabrück"
MyAr = Split(fnd, "/")
For i = LBound(MyAr) To UBound(MyAr)
Set FoundCell = myRange.Find(what:=MyAr(i), after:=LastCell)
If Not FoundCell Is Nothing Then
FirstFound = FoundCell.Address
End If
Set rng = FoundCell
Do Until FoundCell Is Nothing
Set FoundCell = myRange.FindNext(after:=FoundCell)
Set rng = Union(rng, FoundCell)
If FoundCell.Address = FirstFound Then Exit Do
Loop
Next i
If Not rng Is Nothing Then
rng.EntireRow.Interior.ColorIndex = 3
End If
End Sub
For example with this code I can find and highlight all the "Osnabrück" but it doesn't highlight any Hugo or VW.
It's because you're only doing the highlight once at the very end of the code, and the last choice in your array happens to be osnabruck.
You need to move
just before
so that it'll do if for each element in the array.