Compare two sentences and highlight in different color

[vlnmca]

Hi all,

I want to compare two cells in excel using macro and populate the difference after executing the macro.

If any body has worked on this requesting your help in this regard.

this is the my house	this is huge the house
tests are normal	tests normal
there is a small pot	this is pot
this is sum	this is huge sum
Just be to change addresses in the below to match your worksheet.	Just be sure to the change addresses in the lines to match worksheet.
Since added requirements in the comments below, I modified the code to also print out the list of red-highlighted phrases in column C. If you want this list elsewhere, you'll have adjust the address in the last section of the code. I also improved code	Since you added requirements in the comments below, I modified the code to also print out the list of red-highlighted phrases in column C. If you want this list elsewhere, you'll have to adjust the address in the last section of the code. I also improved the highlighting code

<tbody>
</tbody>

 

[vlnmca]

Kindly remove this thread from this forum

 

[vlnmca]

Hi,

I have cross posted in the below forum.


Compare two sentences and highlight in different color

Compare two sentences and highlight in different color

Compare two sentences and highlight in different color

Compare two sentences and highlight in different color using VBA

 

[MickG]

If you're still looking for some code, Try this for data in column A and B.

[COLOR="Navy"]Sub[/COLOR] MG17Sep19
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] SpA         [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Sp1         [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Sp2         [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Sw          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oMax        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]


[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Sp1 = Split(Dn.Value, " "): Sp2 = Split(Dn.Offset(, 1).Value, " ")
    SpA = Array(Sp1, Sp2)
    oMax = Application.Max(UBound(Sp1), UBound(Sp2))
[COLOR="Navy"]Dim[/COLOR] Temp            [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] A               [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] B               [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] St              [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] W               [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rw              [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Fex             [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
ReDim Preserve Sp1(0 To oMax * 2)
ReDim Preserve Sp2(0 To oMax * 2)
SpA = Array(Sp1, Sp2)


[COLOR="Navy"]For[/COLOR] n = 0 To oMax * 2
[COLOR="Navy"]If[/COLOR] Not SpA(0)(n) = "" And Not SpA(0)(n) = SpA(1)(n) [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] Rw = n To UBound(SpA(1))
        [COLOR="Navy"]If[/COLOR] SpA(0)(n) = SpA(1)(Rw) [COLOR="Navy"]Then[/COLOR]
            Fex = 0
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]ElseIf[/COLOR] SpA(1)(n) = SpA(0)(Rw) [COLOR="Navy"]Then[/COLOR]
            Fex = 1
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
     [COLOR="Navy"]Next[/COLOR] Rw
    
     [COLOR="Navy"]If[/COLOR] Fex = 1 [COLOR="Navy"]Then[/COLOR]
           [COLOR="Navy"]For[/COLOR] B = n To oMax * 2 - 1
                Temp = SpA(1)(B + 1)
                SpA(1)(B + 1) = SpA(1)(n)
                SpA(1)(n) = Temp
           [COLOR="Navy"]Next[/COLOR] B
      
     [COLOR="Navy"]ElseIf[/COLOR] Fex = 0 [COLOR="Navy"]Then[/COLOR]
           [COLOR="Navy"]For[/COLOR] A = n To oMax * 2 - 1
                Temp = SpA(0)(A + 1)
                SpA(0)(A + 1) = SpA(0)(n)
                SpA(0)(n) = Temp
           [COLOR="Navy"]Next[/COLOR] A
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If


[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]For[/COLOR] n = 0 To oMax * 2
    [COLOR="Navy"]If[/COLOR] SpA(0)(n) = "" And Not SpA(0)(n) = SpA(1)(n) [COLOR="Navy"]Then[/COLOR]
        SpA(1)(n) = Chr(96) & SpA(1)(n)
    [COLOR="Navy"]ElseIf[/COLOR] SpA(1)(n) = "" And Not SpA(0)(n) = SpA(1)(n) [COLOR="Navy"]Then[/COLOR]
        SpA(0)(n) = Chr(96) & SpA(0)(n)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Dim[/COLOR] strg1 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Strg2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]For[/COLOR] n = 0 To oMax * 2
    [COLOR="Navy"]If[/COLOR] Not SpA(0)(n) = "" [COLOR="Navy"]Then[/COLOR] strg1 = strg1 & " " & SpA(0)(n)
    [COLOR="Navy"]If[/COLOR] Not SpA(1)(n) = "" [COLOR="Navy"]Then[/COLOR] Strg2 = Strg2 & " " & SpA(1)(n)
[COLOR="Navy"]Next[/COLOR] n
Dn.Value = Mid(strg1, 2)
Dn.Offset(, 1) = Mid(Strg2, 2)
strg1 = "": Strg2 = ""


[COLOR="Navy"]For[/COLOR] Ac = 0 To 1
    [COLOR="Navy"]For[/COLOR] n = 1 To Len(Dn.Offset(, Ac).Value)
        [COLOR="Navy"]If[/COLOR] Dn.Offset(, Ac).Characters(n, 1).Text = Chr(96) [COLOR="Navy"]Then[/COLOR]
            Dn.Offset(, Ac).Characters(n, 1).Font.ColorIndex = 2
            Fd = True
        [COLOR="Navy"]ElseIf[/COLOR] Dn.Offset(, Ac).Characters(n, 1).Text = " " [COLOR="Navy"]Then[/COLOR]
            Fd = False
        [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]If[/COLOR] Fd And Not Dn.Offset(, Ac).Characters(n, 1).Text = Chr(96) [COLOR="Navy"]Then[/COLOR] Dn.Offset(, Ac).Characters(n, 1).Font.ColorIndex = 3
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[vlnmca]

Dear Mick,

For single lines comparison it is working fine, but if I define like Paragraph in single cell current macro hangs. I am giving an example as below. Kindly guide me how to do this.

Cell A1 contains below five lines.

1. Press C from the Transaction List to access the Cheque Activity screen.
2. Select ‘Scan Now’ to begin scanning
• Related Topics:
o Scanned Cheque Summary
o Unscanable Cheque

Cell B1 contains below six lines

1. Press C from the Transaction to access the Cheque Activity screen.
2. Select ‘Scan Now’ to begin scanning
• Related Topics:
o Scanned Cheque
o Unused Cheque
3. Place cheques in the scanner

 

[MickG]

1. Press C from the Transaction `List to access the Cheque Activity screen.	1. Press C from the Transaction to access the Cheque Activity screen.
2. Select ‘Scan Now’ to begin scanning	2. Select ‘Scan Now’ to begin scanning
• Related Topics:	• Related Topics:
o Scanned Cheque `Summary	o Scanned Cheque
o `Unscanable Cheque	o `Unused Cheque
	`3. `Place `cheques `in `the `scanner

<COLGROUP><COL style="WIDTH: 344pt; mso-width-source: userset; mso-width-alt: 16298" width=458><COL style="WIDTH: 310pt; mso-width-source: userset; mso-width-alt: 14677" width=413><TBODY>
</TBODY>

This is my result if you change the lines below:-

Set Rng = Intersect(Columns("A:B"), Range("A1").CurrentRegion).Resize(, 1) 'Used this
'Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)) 'Instead of this!!!