Hello, I am utilizing the following solution code which was posted in a related closed thread:
Sub rhelm()
Dim Cl As Range, Rng1 As Range, Rng2 As Range
Dim Sp As Variant
Dim i As Long, Pos As Long
With Selection
If .Areas.Count > 1 Then
Set Rng1 = .Areas(1)
Set Rng2 = .Areas(2)
Else
Set Rng1 = .Columns(1)
Set Rng2 = .Columns(2)
End If
End With
For Each Cl In Range(Rng1(1), Rng1.Range("A" & Rows.Count).End(xlUp))
Sp = Split(Cl.Value)
For i = 0 To UBound(Sp)
Pos = InStr(1, Rng2.Range("A" & Cl.Row).Value, Sp(i), vbTextCompare)
If Pos > 0 Then Rng2.Range("A" & Cl.Row).Characters(Pos, Len(Sp(i))).Font.Color = vbRed
Next i
Next Cl
End Sub
The code is working on some cells (highlighting just the similar text from adjacent column), but on some cells it is highlighting all words and wondering if it could be due to formatting issues in column 1 or any other suggestions. Thank you.
Sub rhelm()
Dim Cl As Range, Rng1 As Range, Rng2 As Range
Dim Sp As Variant
Dim i As Long, Pos As Long
With Selection
If .Areas.Count > 1 Then
Set Rng1 = .Areas(1)
Set Rng2 = .Areas(2)
Else
Set Rng1 = .Columns(1)
Set Rng2 = .Columns(2)
End If
End With
For Each Cl In Range(Rng1(1), Rng1.Range("A" & Rows.Count).End(xlUp))
Sp = Split(Cl.Value)
For i = 0 To UBound(Sp)
Pos = InStr(1, Rng2.Range("A" & Cl.Row).Value, Sp(i), vbTextCompare)
If Pos > 0 Then Rng2.Range("A" & Cl.Row).Characters(Pos, Len(Sp(i))).Font.Color = vbRed
Next i
Next Cl
End Sub
The code is working on some cells (highlighting just the similar text from adjacent column), but on some cells it is highlighting all words and wondering if it could be due to formatting issues in column 1 or any other suggestions. Thank you.