Highlight text differences in Excel cells

For controlled documents it is bets to allow reviewers to see the change in documents – this is managed in Word with the change tracking and review features. Unfortunately, Excel does not have this capability.

After some searching I have figured out how to do this using the built in Excel Inquire add-in and some Visual Basic.

• 1. Supported Excel Versions

First, make sure that you have Office Pro Plus 2013 or Office 365 ProPlus.

* I found hints that Office 2016 also supports this, but I did not research it – please let me know if it does!

• 2. Enable Excel Inquire add-in for compare feature

Follow this support.office.com link to enable the add-in.

The add-in comes with Excel, there is no worry about downloading from an external source.

• 3. Excel Compare Files feature

Surprisingly the best method to do this was found on the Microsoft Support.office.com site (link).

• 4. Highlight differences in Red with VB script

I found a VB script here (link) that allows me to highlight the different text for easier review:

Simply follow these instructions and you have delta text in RED!

• 5. Make Differences Bold by editing the VB Script

This is the code from the link above with my edits in italics and underlined:

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
xCell2.Characters(1, J – 1).Font.Bold = True
End If
Else
If J <= Len(xCell2.Value2) Then
xCell2.Characters(J, Len(xCell2.Value2) – J + 1).Font.Color = vbRed
xCell2.Characters(J, Len(xCell2.Value2) – J + 1).Font.Bold = True
End If
End If
End If

I hope this helps!