compare text and highlight difference

Question

I have text in two cells with different text. I'm attempting to identify the diff (difference between the text: text that is added or missing) between the two cells.

Example :

A1:ThisisasystemgeneratedmentanddoesnotrequiresignatureAnyunauthorizedusedisclosuredisseminationoringofthisdocumentisstrictlyprohibitedandmaybeunlawful

B1:Thisisasystemgenerateddocumentanddoesnotrequiresignatureunauthorizedusedisclosuredisseminationorcopyingofthisdocumentisstrictlyprohibitedandmaybeunful

Both cells A1 and B1 should highlight only textual differences. How do I implement this?

Answer 1

This is actually a pretty tricky scenario, but here you go:

Public Sub FindDistinctSubstrings()
    Dim a$, b$, i&, k&, rA As Range, rB As Range
    Set rA = [a1]: a = rA
    Set rB = [b1]: b = rB
    k = Len(a): If Len(b) > k Then k = Len(b)
    Do
        i = i + 1
        If Mid$(a, i, 1) <> Mid$(b, i, 1) Then
            Align i, a, b, rA, rB
        End If
        DoEvents
    Loop Until i > k
    k = Len(a): If Len(b) > k Then k = Len(b)
    For i = 1 To k
        If Mid$(a, i, 1) = "." Then rB.Characters(i, 1).Font.Color = vbRed
        If Mid$(b, i, 1) = "." Then rA.Characters(i, 1).Font.Color = vbRed
    Next
    Do
        k = InStr(rA, "."): If k Then rA.Characters(k, 1).Delete
    Loop Until k = 0
    Do
        k = InStr(rB, "."): If k Then rB.Characters(k, 1).Delete
    Loop Until k = 0
End Sub
Private Sub Align(k&, a$, b$, rA As Range, rB As Range)
    Dim i&, iMax&, nI&, nMaxI&, j&, jMax&, nJ&, nMaxJ&
    Const LOOK_AHEAD_BUFFER = 30
    For i = 0 To LOOK_AHEAD_BUFFER
        nI = CountMatches(Space$(i) & Mid$(a, k, LOOK_AHEAD_BUFFER), Mid$(b, k, LOOK_AHEAD_BUFFER))
        If nI > nMaxI Then
            nMaxI = nI: iMax = i
        End If
    Next
    For j = 0 To LOOK_AHEAD_BUFFER
        nJ = CountMatches(Mid$(a, k, LOOK_AHEAD_BUFFER), Space$(j) & Mid$(b, k, LOOK_AHEAD_BUFFER))
        If nJ > nMaxJ Then
            nMaxJ = nJ: jMax = j
        End If
    Next
    If nMaxI > nMaxJ Then
        a = Left$(a, k - 1) & String$(iMax, ".") & Mid$(a, k)
        rA = a: k = k + iMax
    Else
        b = Left$(b, k - 1) & String$(jMax, ".") & Mid$(b, k)
        rB = b: k = k + jMax
    End If
End Sub
Private Function CountMatches(a$, b$) As Long
    Dim i&, k&, c&
    k = Len(a): If Len(b) < k Then k = Len(b)
    For i = 1 To k
        If Mid$(a, i, 1) = Mid$(b, i, 1) Then c = c + 1
    Next
    CountMatches = c
End Function