Compare words in 2 cells highlighting the same words or different words

[burniksapwet]

I found this code online guys and I need help updating it.

Sub Macro1()
Dim xRg1 As Range
Dim xRg2 As Range
Dim xTxt As String
Dim xCell1 As Range
Dim xCell2 As Range
Dim I As Long
Dim J As Integer
Dim xLen As Integer
Dim xDiffs As Boolean
On Error Resume Next
If ActiveWindow.RangeSelection.count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
lOne:
Set xRg1 = Application.InputBox("Range A:", "Kutools for Excel", xTxt, , , , , 8)
If xRg1 Is Nothing Then Exit Sub
If xRg1.Columns.count > 1 Or xRg1.Areas.count > 1 Then
MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Kutools for Excel"
GoTo lOne
End If
lTwo:
Set xRg2 = Application.InputBox("Range B:", "Kutools for Excel", "", , , , , 8)
If xRg2 Is Nothing Then Exit Sub
If xRg2.Columns.count > 1 Or xRg2.Areas.count > 1 Then
MsgBox "Multiple ranges or columns have been selected ", vbInformation, "Kutools for Excel"
GoTo lTwo
End If
If xRg1.CountLarge <> xRg2.CountLarge Then
MsgBox "Two selected ranges must have the same numbers of cells ", vbInformation, "Kutools for Excel"
GoTo lTwo
End If
xDiffs = (MsgBox("Click Yes to highlight similarities, click No to highlight differences ", vbYesNo + vbQuestion, "Kutools for Excel") = vbNo)
Application.ScreenUpdating = False
xRg2.Font.ColorIndex = xlAutomatic
For I = 1 To xRg1.count
Set xCell1 = xRg1.Cells(I)
Set xCell2 = xRg2.Cells(I)
If xCell1.Value2 = xCell2.Value2 Then
If Not xDiffs Then xCell2.Font.Color = vbRed
Else
xLen = Len(xCell1.Value2)
For J = 1 To xLen
If Not xCell1.Characters(J, 1).Text = xCell2.Characters(J, 1).Text Then Exit For
Next J
If Not xDiffs Then
If J <= Len(xCell2.Value2) And J > 1 Then
xCell2.Characters(1, J - 1).Font.Color = vbRed
End If
Else
If J <= Len(xCell2.Value2) Then
xCell2.Characters(J, Len(xCell2.Value2) - J + 1).Font.Color = vbRed
End If
End If
End If
Next
Application.ScreenUpdating = True
End Sub

What this will do is lets say you have this statement in column A and column B

A____________________________B
Jason is driving there_______Jason is walking there

If you compare A to B then it will highlight that the same words "Jason is" and it will turn the text red. What i want it to do is basically search of every word in column A (or whatever column you select) and not just turn the text up to the point that they are similar. So in this instance I want the macro to work to were it will highlight and turn the text red for the word there because it is present in both columns regardless of whats in between them.

The result will be like this

A____________________________B
Jason is driving there_______Jason is walking there

Im hoping to get something like this
A____________________________B
Jason is driving there_______Jason is walking there

I hope someone will be able to help me out with this problem and would like to thank all who are willing to help in advance. Thank you so much.

 

[burniksapwet]

Here is the link where I got the code from.

How to compare two strings for similarity or highlight differences in Excel?

ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.

www.extendoffice.com

 

[burniksapwet]

Bump

 

[Peter_SSs]

Do the words have to be in the same place in the text?
Also, what about repeated words in one (or both) of the columns?
For example, what is the exact expected output for this?

burniksapwet.xlsm
	A	B
1	Jason is driving there	Over there Jason is walking & his dog is too
Sheet1

 

[burniksapwet]

Do the words have to be in the same place in the text?
Also, what about repeated words in one (or both) of the columns?
For example, what is the exact expected output for this?

burniksapwet.xlsm
	A	B
1	Jason is driving there	Over there Jason is walking & his dog is too
Sheet1

Doesn’t have to be in the same place. Just anywhere you could see the word. And yes I guess it should highlight if there’s duplicates as well. So in the example you gave the words “there, Jason, is, and is should highlight red.

Thank you so much for looking into this and possibly helping me out. Hope it’s doable. Thank you.

 

[Peter_SSs]

Assuming you are on a Windows system, try this with a copy of your workbook.

Sub Matching_Words_in_B()
  Dim RX As Object, M As Object
  Dim a As Variant, itm As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  Application.ScreenUpdating = False
  With Range("A1", Range("B" & Rows.Count).End(xlUp))
    .Columns(2).Font.Color = vbBlack
    a = .Value
    For i = 1 To UBound(a)
      RX.Pattern = "\b(" & Replace(a(i, 1), " ", "|") & ")\b"
      Set M = RX.Execute(a(i, 2))
      With .Cells(i, 2)
        For Each itm In M
          .Characters(itm.firstindex + 1, Len(itm)).Font.Color = vbRed
        Next itm
      End With
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

 

[burniksapwet]

Assuming you are on a Windows system, try this with a copy of your workbook.

Sub Matching_Words_in_B()
  Dim RX As Object, M As Object
  Dim a As Variant, itm As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  Application.ScreenUpdating = False
  With Range("A1", Range("B" & Rows.Count).End(xlUp))
    .Columns(2).Font.Color = vbBlack
    a = .Value
    For i = 1 To UBound(a)
      RX.Pattern = "\b(" & Replace(a(i, 1), " ", "|") & ")\b"
      Set M = RX.Execute(a(i, 2))
      With .Cells(i, 2)
        For Each itm In M
          .Characters(itm.firstindex + 1, Len(itm)).Font.Color = vbRed
        Next itm
      End With
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

Thank you so much. This is exactly what we are looking for. Now if you would be so kind I would like to request a variation of this script. Can it be updated so that there is a pop up asking ask which we column we would like to compare? Thank you so much once again. This has been very helpful to us. Thank you.

 

[Peter_SSs]

Can it be updated so that there is a pop up asking ask which we column we would like to compare?

Can you spell out in more detail (perhaps with examples) exactly what you mean by that?

 

[burniksapwet]

Sorry for the confusion. Just like with the code I listed. If you test it, it will ask you to select which columns you want to compare. I’m asking this because our information will not always be in column a and b. The might be in d e, f g, etc. so I would like to have an option to select where the 2 comparing data’s will be from. Thank you.

 

[Peter_SSs]

1. Will the two columns always be immediately next to each other?
   
   
2. Will it always be the second one of the two columns where the words need to be coloured?