VBA code to highlight individual changed words within Cells

Does anyone know if there is VBA code to highlight individual words that have changed in cells between 2 versions of the same sheet? I found code that highlights the whole cell where changes have been made (where the result sheet is named "Def" and the Original sheet is named "Abc") as follows :

Sub comparesheets()
For Each cl In Sheets("Def").UsedRange
If cl.Value <> Sheets("Abc").Cells(cl.Row, cl.Column) Then
cl.Font.Color = RGB(0, 0, 255)
End If
Next cl
End Sub

...But I would like to show the actual change in colour rather than the whole cell. e.g. if cell A1 on sheet "Abc" shows "Heathrow Gate 24" and A1 on sheet "Def" has been change to "Heathrow Gate 32", I want A1 on "Def" to show as "Heathrow Gate 32"

Any ideas?

try this

Please Login or Register  to view this content.

Kalak

Many thanks for your suggestion - I just need a small "tweak" to the code. I think the "mid" statement needs to be changed slightly and I am not sure how. Certain letters in the amended sheet do not show in colour if they exist in the original text - for example if "Heathrow Gate" was changed to "Heathrow Lane" the "L" and the "n" in Lane would appear in colour but "a" and "t" would not as they existed in the original word. Is there a way that the whole words can be coloured if the word is changed?

Apologies for the delay in replying - I have rarely used this site and thought I had replied to the thread yesterday but nothing appeared, hence the reply here.

Many thanks M

this is only lightly tested, and may need some modification.
but I'm out of time now, so test it anyway and see if it does OK or not

Please Login or Register  to view this content.