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!
