CODEWITHSUNDEEP
excel
zwornik
zwornik

Reputation: 349

Excel highlight part of text in cell that differs from other cell

It there a way to achieve something like this in Excel:

enter image description here

I am looking for way to highlight part of text in cell that differs from other cell. Difference can appear at any position in cell.

Upvotes: 0

Views: 97

Answers (1)

user4039065
user4039065

Reputation:

Try this sub procedure.

Sub Macro5()
    Dim i As Long, j As Long, p As Long, arr1 As Variant, arr2 As Variant

    With Worksheets("sheet4")
        For i = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
            'remove previous character formatting
            .Cells(i, "A") = .Cells(i, "A").Value2
            .Cells(i, "B") = .Cells(i, "B").Value2

            'split cell values into arrays on a 'pipe' delimiter
            arr1 = Split(.Cells(i, "A").Value2, Chr(124))
            arr2 = Split(.Cells(i, "B").Value2, Chr(124))

            'found in column A; not found in column B
            For j = LBound(arr1) To UBound(arr1)
                If IsError(Application.Match(arr1(j), arr2, 0)) Then
                    p = InStr(1, .Cells(i, "A").Value2, arr1(j), vbTextCompare)
                    With .Cells(i, "A").Characters(Start:=p, Length:=Len(arr1(j))).Font
                        .Bold = True
                        .Color = vbRed
                    End With
                End If
            Next j

            'found in column B; not found in column A
            For j = LBound(arr2) To UBound(arr2)
                If IsError(Application.Match(arr2(j), arr1, 0)) Then
                    p = InStr(1, .Cells(i, "B").Value2, arr2(j), vbTextCompare)
                    With .Cells(i, "B").Characters(Start:=p, Length:=Len(arr2(j))).Font
                        .Bold = True
                        .Color = vbRed
                    End With
                End If
            Next j
        Next i
    End With
End Sub

enter image description here

Upvotes: 1

Related Questions