Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Vbscript - String comparison which highlights actual differences

Posted on 2010-09-23
Medium Priority
Last Modified: 2012-05-10
I would assume that this may be a very difficult function to code.  I'm looking for a function in vbscript to compare two strings and highlight the actual difference in red (within an excel cell).

For example, I have two strings:
1:    \TABLE_E23\3YRFTJ\9TES_3R_KLM;TP=1.8;LS=11,9;~TABLE_E23
2:    \TABLE_E23\3YRFTJ\9TES_3R_KLM;LS=11,9;~TABLE_E23
For this example, it would color TP=1.8 in the first string red because it does not exist in the second string.

I would assume that the inputs for the function would be each row and column for the two cells as well as the sheet.  But I really don't care if you need the inputs to be something different, I can change my code to work with it.  

The function would search the two strings and highlight the exact letters that are missing or different from each.

Another example would be:
1:    \TABLE_E23\3YRFTJ\9TES_3R_KLM;TP=1.8;LS=11,9;~TABLE_E23
2:    \TABLE_E23\3YRFTJ\9TES_3R_JRK;TP=1.8;LS=11,9;~TABLE_E23
For this example, it would highlight "KLM" and "JRK" because they are different between the two strings.

I'm constantly having to make this comparison myself for thousands of lines and human error is very high.  I'm pretty good at coding but this one has me stumped!!

Many, Many thanks to the person that figures this out!
Question by:mattlaltman
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +1
LVL 13

Expert Comment

ID: 33748682
from :
You run CheckAgainstColumnA.
It evaluates the text in Column A and highlights the text that's not in Column B next to it.

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

Sub CheckAgainstColumnA()
    Dim oneCell As Range
    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))
            Call highlightDifference(oneCell.Offset(0, 1), oneCell)
        Next oneCell
    End With
End Sub

Sub highlightDifference(refCell As Range, testCell As Range)
    Dim refString As String, testString As String
    Dim i As Long, startPoint As Long, newPoint As Long
    testCell.Font.ColorIndex = redCIndex
    refString = refCell.Text
    testString = testCell.Text
    startPoint = 1
    For i = 1 To Len(refString)
        newPoint = InStr(startPoint, testString, Mid(refString, i, 1))
        If newPoint <> 0 Then
            testCell.Characters(newPoint, 1).Font.ColorIndex = blackCIndex
            startPoint = newPoint + 1
        End If
    Next i
End Sub

Open in new window

LVL 13

Expert Comment

ID: 33748759
After testing on your solution it's just a tid bit off. ha
But it's a start.  Good code to begin with.  I imagine a little tweaking is all it would need.
I don't have time right now to work with it, but if the ? is still open in the morning I'll see what I can do with it.  
Other experts can feel free to tweak it.

Author Comment

ID: 33748935
hmmm I tried that and it just colors everything red from both lines...  
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 33749089
Hi mattlaltman,

I think the following approach will do the deal:

1. Split the strings to elements. This can be done by Split function or by worksheet "Text to columns" command. You need to decide what delimiters should be specified. It follows from your examples that delimiters are "_" and ";". What about "\"?

2. Compare each element against elements of the other string. You can use Instr function to check element occurrence in the while string but this method can return nonexistent matches. The more accurate way is to loop through array of elements or use Countif worksheet function.

3. Apply color to non-matching elements as in code provided by MWGainesJR.

BR, Alex
LVL 50

Expert Comment

by:Dave Brett
ID: 33749916
The code from MWGainesJR looks like the right apporach for me

fwiw, Spreadsheet Advantage - 30 day free trial from - lets you compare two worksheets (in the same or different workbooks) by value, formula or both and produces the output you mention here, ie any differnces are highlighted in red font. I



Author Comment

ID: 33750273
Thanks Dave, but I actually need the code. My script runs outside of excel and just outputs the data there. Plus I'd prefer not to have to buy anything :).
LVL 13

Expert Comment

ID: 33750302
The code I posted works fine for me.  It's not completely accurate but doesn't color everything red. Try switching the columns around.

Author Comment

ID: 33750420
Oh ok I'll try it again then. I had the two strings in column A rows 1 and 2.  But I guess I need them in columns A and B

Accepted Solution

Exceleved earned 1500 total points
ID: 33751667
Hi mattlaltman,

please find the macros in the attached workbook.
Hope you can adapt the code to your script.

BR, Alex
LVL 13

Expert Comment

ID: 33753240
Yes you need strings in A and B.  It compares A1 to B1, A2 to B2 etc.....

Author Comment

ID: 33766495
Sorry it's taken me a few days to get back to this.  I've had some time to look at the code from everyone.  

MWGainesJR, Yes I agree, it's a good start but it's not getting the correct answer all the time.

Exceleved, This is very nice and a good approach.  The only problem is that the example I gave in the code is just one of many very different examples.  There are instances where there maybe only one delimiter in the whole string, so if colors the majority of the string red when there is really only one letter difference.

Dave's add-in looks to be exactly what I need but the problem is that it's an add-in to excel and I actually need the code.  The thing is, when you look closely at the results it will highlight the entire number that different (see attachment).  For example, when comparing 87 to 89 in the secon example, it colors the entire number red (not just the 7 and the 9).  This makes me think that it works a lot like Exceleved's code but just uses delimiters in some way..... hmmmm

But beggars can't be choosers.  I appreciate everyone's help.  I'm not going to accept a solution at the moment in hopes that someone out there may have just a little more advise.  I'll try to manipulated the code as well and see if I can get this to work from some of the examples provided.  

Expert Comment

ID: 33767758
please provide an extended example with all data types you are going to work with.

Author Closing Comment

ID: 33880549
Thanks for this code, It's definately going to get me on the right track.  I will continue working on it to see if I can improve on the concept.  

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question