Can a vba macro be built that highlights search strings found within an array of cells containing line breaks?

[aruthir]

Hi Excel Pros,

I was hoping that someone could help guide me on whether it would be possible to solution a vba macro to do the following.

Per the example below... I have an array of string values (10 of them in example below) that I'd want to match against another array (Column A & B) that has the information in cells that sometimes contains line breaks in them.

What I'd love to figure out how to solution is whether any vba experts can think of a way for me to build a macro that can check each string character against the array of values found in columns A and column B; and where matches are found, highlight both the search string value as well as the string value itself in the array that it was matched up against. The ideal result can be seen in the Expected Results.

I'm thinking that I could perhaps leverage some kind of formula like =ISNUMBER(SEARCH(x,y)) as a very basic starting point to determine if the string exists somewhere in the cells with line breaks, but I'm unsure how to leverage vba to properly automate highlighting the matching strings to make reviewing the information a lot simpler. Thank you in advance to anyone with any advice, suggestions, and/or hopefully solution to my dilemma!

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Data Set[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD][/TD]
[TD]String1[/TD]
[TD]String2[/TD]
[TD]String3[/TD]
[TD]String4[/TD]
[TD]String5[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]Adam
Bob
Charlie[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69"]Ava
Beth
Caroline[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Arnold[/TD]
[TD]Brian[/TD]
[TD]Charlie[/TD]
[TD]David[/TD]
[TD]Ernest[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]David
Ernest
Frank[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69"]Daisy
Esther
Freda[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Ava[/TD]
[TD]Beth[/TD]
[TD]Chuck[/TD]
[TD]Daisy[/TD]
[TD]Frank[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]Expected Result[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]Adam
Bob
Charlie[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69"]Ava
Beth
Caroline[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]Arnold[/TD]
[TD]Brian[/TD]
[TD]Charlie[/TD]
[TD]David[/TD]
[TD]Ernest[/TD]
[/TR]
[TR]
[TD][TABLE="width: 79"]
<tbody>[TR]
[TD="class: xl65, width: 79"]David
Ernest
Frank[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69"]Daisy
Esther
Freda[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]Ava[/TD]
[TD]Beth[/TD]
[TD]Chuck[/TD]
[TD]Daisy[/TD]
[TD]Frank[/TD]
[/TR]
</tbody>[/TABLE]

 

[Osvaldo Palmeiro]

Hi.

See if this code could be a start point.

Sub HighlightStrings()
 Dim So As Range, Sd As Range, firstAddress As String, pos As Long
  For Each So In Range("D1:H2")
   Set Sd = Range("A1:B2").Find(So, LookIn:=xlValues, lookat:=xlPart)
    If Not Sd Is Nothing Then
      firstAddress = Sd.Address
       Do
        So.Font.ColorIndex = 3
         With Sd
           pos = InStr(.Value, So.Value)
           .Characters(pos, Len(So.Value)).Font.ColorIndex = 3
         End With
        Set Sd = Range("A1:B2").FindNext(Sd)
       Loop While Not Sd Is Nothing And Sd.Address <> firstAddress
    End If
  Next So
End Sub

 

[aruthir]

Hi Osvaldo,

You're a saint. Thank you so much for taking time out of your day to help me solution this issue. It worked exactly as expected!