Consulting

Results 1 to 18 of 18

Thread: Compare two sentences and highlight in different color

  1. #1
    VBAX Newbie
    Joined
    Sep 2014
    Posts
    3
    Location

    Compare two sentences and highlight in different color

    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.
    Attached Files Attached Files

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    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)
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    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.

  6. #6
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    ..... Yes it is a very small world these days.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7

  8. #8
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    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?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844

  10. #10
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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
    Last edited by snb; 09-13-2014 at 08:05 AM.

  11. #11
    VBAX Newbie
    Joined
    Sep 2014
    Posts
    3
    Location
    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.
    Attached Files Attached Files

  12. #12
    @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 ???

  13. #13

  14. #14
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    I'm out then...
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  15. #15
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.)

  16. #16
    VBAX Newbie
    Joined
    Sep 2014
    Posts
    3
    Location
    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.

  17. #17
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by vlnmca View Post
    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.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  18. #18
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    @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
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •