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: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.

ksigcajun · #6 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 · #7 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 · #8 03-16-2015, 01:14 PM

Quote:

Originally Posted by Snakehips

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???

PHP Code:


			
Sub Compare_Text()
Set ws = ActiveSheet
ws.forms("Textbox3").TextFrame.Characters.Font.ColorIndex = 1
Arry1 = Split(ws.forms("Textbox1").TextFrame.Characters.Text, " ")
Arry2 = Split(ws.forms("Textbox2").TextFrame.Characters.Text, " ")
If UBound(Arry1) >= UBound(Arry2) Then
    Arry3 = Arry1
    Arry4 = Arry2
    ws.forms("Textbox3").TextFrame.Characters.Text = ws.forms("Textbox1").TextFrame.Characters.Text
    Else
    Arry3 = Arry2
    Arry4 = Arry1
    ws.forms("Textbox3").TextFrame.Characters.Text = ws.forms("Textbox2").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.forms("Textbox3").TextFrame.Characters(tc - wcc, wcc).Font.ColorIndex = 3
     On Error GoTo 0
    End If
Next i
End Sub

Code posted. I just changed the textbox names to match mine on the spreadsheet.

It breaks at ws.forms("Textbox3").TextFrame.Characters.Font.Col orIndex = 1

I'm using ActiveX Control Textbox. Could that be the issue here?

Snakehips · #9 03-16-2015, 01:31 PM

Yes code for activex will need to be somewhat different.
Given what I said in the last post, is this approach worth pursuing?

I cannot respond with code for a while as not at PC.

ksigcajun · #10 03-16-2015, 01:41 PM

Quote:

Originally Posted by Snakehips

Yes code for activex will need to be somewhat different.
Given what I said in the last post, is this approach worth pursuing?

I cannot respond with code for a while as not at PC.

Can you provide a quick example of what you were referring to about the paragraphs? It might be a few paragraphs tops being used.

Snakehips · #11 03-16-2015, 03:56 PM

My name is Bill and I am a professional bank robber with black hair and a red nose.

My name is Dan and I am bank robber with black hair and a red nose.
My name is Bill and I am a professional bank robber with black hair and a red nose.

Anything after the word professional differs in the sort of comparison my code was making. So not really much good?

Re ActiveX text boxes, as far as I know it is all or nothing with the font colour so you could not colour individual words anyway.

Sorry it's not much help after all.

Maybe you have to get your comparison done outside of Excel?

ksigcajun · #12 03-17-2015, 05:10 AM

Quote:

Originally Posted by Snakehips

My name is Bill and I am a professional bank robber with black hair and a red nose.

My name is Dan and I am bank robber with black hair and a red nose.
My name is Bill and I am a professional bank robber with black hair and a red nose.

Anything after the word professional differs in the sort of comparison my code was making. So not really much good?

Re ActiveX text boxes, as far as I know it is all or nothing with the font colour so you could not colour individual words anyway.

Sorry it's not much help after all.

Maybe you have to get your comparison done outside of Excel?

I've inserted three text boxes via Shapes. I will be dealing with some instances when its just a sentence or two. Im thinking your code would still be very helpful.

Do you think the comparison is better done in Word?

Snakehips · #13 03-17-2015, 05:21 AM

I don't use word at all so have no experience of it but it would appear to have the capability to compare text.

A quick Google came up with this amongst hundreds of others.... http://www.linglom.com/it-support/co...icrosoft-word/

ksigcajun · #14 03-18-2015, 01:45 PM

Quote:

Originally Posted by Snakehips

I don't use word at all so have no experience of it but it would appear to have the capability to compare text.

A quick Google came up with this amongst hundreds of others.... http://www.linglom.com/it-support/co...icrosoft-word/

Thanks for all the great info. I really appreciate your time and effort.

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