Comparing text and highlighting differences

ksigcajun · #1 03-04-2015, 11:15 AM

I've been banging my head trying to come up with a Macro to work correctly. Ive tried searching the forum, but can't come up with anything useful.

Basically I want to be able to see the difference between Textbox1 and Textbox2 and display any difference would be highlighted in red or font change to red in Textbox3.

I would love for it to work like this.

User would enter into Textbox 1 = Hello my name is Bob and I work in the automotive industry for 30 years.

User would enter into Textbox 2 = Hello my name is Dan and I work in the medical industry for 30 years.

Textbox 3 would display and show differences = Hello my name is Dan and I work in the medical industry for 30 years.

Any help would be appreciated!

ksigcajun · #2 03-16-2015, 09:40 AM

Anyone got an idea or point me in the right direction?

Thanks!

Snakehips · #3 03-16-2015, 11:06 AM

ksigcajun,

These are textboxes inserted in a sheet?

Are the texts to be compared going to differ in a more complex manner than illustrated in your first post? Eg...

I am Dan the man.

Bob the man I am.

or

There are words in this textbox.
Words there are far more in this textbox.

Snakehips · #4 03-16-2015, 11:42 AM

The following code will deal with your posted example or the slight variation below but after that it will get a whole lot harder.

My name is Bill and I am a professional bank robber .
My name is Dan and I am a police officer.
My name is Bill and I am a professional bank robber .

Code:

Sub Compare_Text()

Set ws = ActiveSheet
ws.Shapes("Textbox 3").TextFrame.Characters.Font.ColorIndex = 1

Arry1 = Split(ws.Shapes("Textbox 1").TextFrame.Characters.Text, " ")
Arry2 = Split(ws.Shapes("Textbox 2").TextFrame.Characters.Text, " ")

If UBound(Arry1) >= UBound(Arry2) Then
    Arry3 = Arry1
    Arry4 = Arry2
    ws.Shapes("Textbox 3").TextFrame.Characters.Text = ws.Shapes("Textbox 1").TextFrame.Characters.Text
    Else
    Arry3 = Arry2
    Arry4 = Arry1
    ws.Shapes("Textbox 3").TextFrame.Characters.Text = ws.Shapes("Textbox 2").TextFrame.Characters.Text
End If

For i = 0 To UBound(Arry1)
    wcc = Len(Arry3(i)) + 1
    tc = tc + wcc
    On Error Resume Next
    If Not Arry3(i) = Arry4(i) Then
         ws.Shapes("Textbox 3").TextFrame.Characters(tc - wcc, wcc).Font.ColorIndex = 3
     On Error GoTo 0
    End If
Next i

End Sub

Hope that helps give you a start.

ksigcajun · #5 03-16-2015, 12:52 PM

Quote:

Originally Posted by Snakehips

The following code will deal with your posted example or the slight variation below but after that it will get a whole lot harder.

My name is Bill and I am a professional bank robber .
My name is Dan and I am a police officer.
My name is Bill and I am a professional bank robber .

Code:

Sub Compare_Text()
 
Set ws = ActiveSheet
ws.Shapes("Textbox 3").TextFrame.Characters.Font.ColorIndex = 1
 
Arry1 = Split(ws.Shapes("Textbox 1").TextFrame.Characters.Text, " ")
Arry2 = Split(ws.Shapes("Textbox 2").TextFrame.Characters.Text, " ")
 
If UBound(Arry1) >= UBound(Arry2) Then
    Arry3 = Arry1
    Arry4 = Arry2
    ws.Shapes("Textbox 3").TextFrame.Characters.Text = ws.Shapes("Textbox 1").TextFrame.Characters.Text
    Else
    Arry3 = Arry2
    Arry4 = Arry1
    ws.Shapes("Textbox 3").TextFrame.Characters.Text = ws.Shapes("Textbox 2").TextFrame.Characters.Text
End If
 
For i = 0 To UBound(Arry1)
    wcc = Len(Arry3(i)) + 1
    tc = tc + wcc
    On Error Resume Next
    If Not Arry3(i) = Arry4(i) Then
         ws.Shapes("Textbox 3").TextFrame.Characters(tc - wcc, wcc).Font.ColorIndex = 3
     On Error GoTo 0
    End If
Next i
 
End Sub

Hope that helps give you a start.

Awesome! Thats an unbelievable start.
I cleaned it up a little and tried running your macro, but it fails to run.

Im receiving the following message:
"Run-time error '438':
Object doesn't support this property or method"

Is this an Excel issue or the code?

Snakehips · #6 03-16-2015, 01:09 PM

Quote:

Originally Posted by ksigcajun

Awesome! Thats an unbelievable start.
I cleaned it up a little and tried running your macro, but it fails to run.

Im receiving the following message:
"Run-time error '438':
Object doesn't support this property or method"

Is this an Excel issue or the code?

Can you post your 'cleaned up' code and indicate where the error occurs?
Try stepping through the code using F8 key to see where it breaks.

If you are talking paragraphs of text then this will not be that useful as once it gets to the point where one textbox has had a word inserted or deleted all subsequent words will be out of step and therefore will be highlighted???

ksigcajun · #7 03-16-2015, 12:28 PM

Quote:

Originally Posted by Snakehips

ksigcajun,
These are textboxes inserted in a sheet?

Yes, three textboxes (textbox1, textbox2 and textbox3) on the sheet.

Quote:

Are the texts to be compared going to differ in a more complex manner than illustrated in your first post? Eg...

I am Dan the man.

Bob the man I am.

or

There are words in this textbox.
Words there are far more in this textbox.

It's going to differ each time. Its going to be multiparagraphs in textbox1 and in textbox2 will be the updates and in textbox 3 will show the paragraphs and what are the differences.

Does that make any sense? Appreciate the help on this.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Highlighting Text After Sync	lvautier	OneNote	1	02-07-2015 04:02 PM
Comparing dates in legacy control text box	JoeE	Word VBA	2	05-08-2013 11:01 PM
Calculate differences between text cells?	poobear	Excel	2	05-19-2012 06:43 AM
Comparing 2 columns of Text	Berk21	Excel	9	02-03-2012 12:19 PM
Text Highlighting in Yellow ???	mark4man	Publisher	0	12-15-2005 06:46 PM