PDA

View Full Version : Compare two sentences and highlight in different color



vlnmca
09-12-2014, 09:33 AM
Hi all,

I want to compare two cells in excel using macro and populate the difference after executing the macro. I have attached my compare diff.xls how my cells has to be highlighted after executing the macro.

If any body has worked on this requesting your help in this regard.

Thanks and in advance.

Aussiebear
09-12-2014, 04:40 PM
Since we are comparing cells against one another, one has to be considered the "base" cell, as in this is the one to which the other is compared against. In your example the first five rows both cells contain highlighted differences. To what are they being compared against? If its cell A1 against cell B1 then only cell A1 should contain the highlighted differences.

SamT
09-12-2014, 05:20 PM
Word to word compare, also word count. In any two sentence if there is not matching word in the other, highlight the word without a match. Also if one sentence has two intances of a word, but the other only has one instance, highlight the extra instance.

Sounds too tough for me, Ted.

Aussiebear
09-12-2014, 08:07 PM
Maybe Sam but this came from one of our highly respected members



Option Explicit
'Option Compare Text

Const redCIndex As Long = 3
Const blackCIndex As Long = 0

Sub CheckAgainstColumnA()
Dim CSensitivity As Long
Dim oneCell As Range
Select Case MsgBox("Case Sensitive", vbYesNo)
Case Is = vbCancel
Exit Sub
Case Is = vbYes
CSensitivity = 0
Case Is = vbNo
CSensitivity = 1
End Select

With ThisWorkbook.Sheets("Sheet1").Range("A:A"): Rem adjust
For Each oneCell In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
oneCell.Font.Color = blackCIndex
oneCell.Offset(0, 1).Font.ColorIndex = blackCIndex
Call highlightDifference(oneCell, oneCell.Offset(0, 1), CSensitivity)
Call highlightDifference(oneCell.Offset(0, 1), oneCell, CSensitivity)
Next oneCell
End With
End Sub



Sub highlightDifference(refCell As Range, testCell As Range, Optional CaseSensitivity As Long)
Rem default caseSenstivity = 0 for case insensitive, set CaseSensitivity = 1

Dim refString As String, testString As String
Dim i As Long, startPoint As Long, newPoint As Long
CaseSensitivity = Sgn(CaseSensitivity) ^ 2

With testCell.Font
.ColorIndex = redCIndex
.FontStyle = "Bold"
End With
refString = refCell.Text
testString = testCell.Text
startPoint = 1
For i = 1 To Len(refString)
newPoint = InStr(startPoint, testString, Mid(refString, i, 1), CaseSensitivity)
If newPoint <> 0 Then
With testCell.Characters(newPoint, 1).Font
.ColorIndex = blackCIndex
.FontStyle = "Regular"
End With
startPoint = newPoint + 1
End If
Next i
End Sub

....and its a great starting point. (Wouldn't you say Mike)

jolivanes
09-12-2014, 09:48 PM
http://www.ozgrid.com/forum/showthread.php?t=190901
Only post in different forums if you make people aware of it so they don't spend time after it might have been solved already.
It's good manners to do so.

Aussiebear
09-13-2014, 12:57 AM
..... Yes it is a very small world these days.

VoG
09-13-2014, 03:56 AM
Cross posted here as well http://www.mrexcel.com/forum/excel-questions/805114-compare-two-sentences-highlight-different-color.html

Aussiebear
09-13-2014, 04:57 AM
vinmca, I know you are currently online and reviewing this thread, so eyes up. Becoming a serial cross poster, has some unwelcome benefits, like for example, no one will want to assist you in the future. You really don't want to become that person do you?

p45cal
09-13-2014, 05:17 AM
cross posted at http://www.excelforum.com/excel-programming-vba-macros/1036662-compare-two-sentences-and-highlight-in-different-color.html too.

snb
09-13-2014, 06:14 AM
Assuming

Column A : the words that have to be highlighted
Column D : sentences that contain words that have to be highlighted
Column H : a helper column


Sub M_snb()
sn = Cells(1).CurrentRegion

Cells(1, 4).CurrentRegion.Offset(, 4) = Evaluate(""" ""&" & Cells(1, 4).CurrentRegion.Address & "&"" """)
For j = 1 To UBound(sn)
Columns(8).Replace " " & sn(j, 1) & " ", " ~" & sn(j, 1) & "~ ", 2
Next

sp = Cells(1, 8).CurrentRegion
Cells(1, 8).CurrentRegion.ClearContents

For j = 1 To UBound(sp)
If InStr(sp(j, 1), "~") Then
st = Split(Trim(sp(j, 1)), "~")
y = Len(st(0))
For jj = 1 To UBound(st) - 1
If jj Mod 2 = 1 Then Cells(j, 4).Characters(y + 1, Len(st(jj))).Font.ColorIndex = 3
y = y + Len(st(jj))
Next
End If
Next
End Sub

vlnmca
09-13-2014, 08:35 AM
Hi Aussiebear/VoG/p45cal,

I am trying out this problem past 1 month I could not able to bring the issue down. so posted in multiple forums so that brilliant people like you can help me to solve this problem.

If you know the answer kindly help me out so that i will not do cross posting it. I am attaching my macro and current excel values which is having paragraph.

Tom Jones
09-13-2014, 12:01 PM
@vlnmca,

You have very sharp lines here and on other forums.
You say, over a month looking to solve the problem but you never thought to pay some professionals ???

snb
09-13-2014, 01:19 PM
crossposted at http://www.excelguru.ca/forums/showthread.php?3505-Compare-two-sentences-and-highlight-in-different-color-using-VBA

Aussiebear
09-13-2014, 03:39 PM
I'm out then...

p45cal
09-13-2014, 04:31 PM
re:"VoG, If you not that much expert in excel macro why are you stopping others from giving wrong comments. Please allow others to give solution for my problem." (quote from another forum)

Vog's not stopping anyone from doing anything. He's only making them aware of what's going on elsewhere; something that you should have done yourself.
It boils down to your consideration for others and more lately, your subsequent attitude, neither of which will encourage others to help.
(Apologies to the moderators here as I'm probably guilty of back-seat moderating as I'm not a moderator here.)

vlnmca
09-14-2014, 12:23 AM
Dear Friends,

If I have hurt any one I am making Apologize for my words made.

I don't know how to remove my thread from multiple forum. Kindly help in this issue.

Aussiebear
09-14-2014, 12:41 AM
If I have hurt any one I am making Apologize for my words made.
vinmca, it is not "for the words you made" but rather "the words not made" that people get quite upset. Every forum that you cross posted in, has a section within their rules (which you had to agree to before joining) regarding cross posting. While we can't stop you from cross posting an issue, we do ask that you indicate that you have done so. People then have a choice to follow up on the other site/s to see how advanced the issue is, rather than give you the time and effort only to find out that the issue has been solved elsewhere.


I don't know how to remove my thread from multiple forum. Kindly help in this issue.
I have forwarded your request to Admin, but I don't think they will agree to do so.

Simon Lloyd
09-14-2014, 11:05 AM
@vlnmca you need to visit every thread you have created on this topic at every forum and post ALL the links to your crossposts in ALL the forums, meanwhile read this http://www.excelguru.ca/content.php?184