unmatched data string
 

I have two strings of data that I compare with a vlookup and then I kick out
the data that doesn't match into two different columns. I am wondering if it
is possible to have the strings of data highlight just the portion of the
string that doesn't match.
For example:
2008JXA800OCT0250 2008JXA800JAN0250

2008JXA850OCT0301 2008JXA850JAN0306

2009JXA900OCT07 2008JXA900JAN07
The 1st string would highlight the OCT and the 2nd string across from it
would highlight the JAN. While the next row the 1st string would highlight
the 1 and the 2nd string across from it would highlight the 6 and in the
third row the 1st string would highlight the 9 (from the 2009) and the 2nd
string across from it would highlight the 8(from the 2008).

Any help is always appreciated. Thanks!

unmatched data string
 

The following macro should do what you want AS LONG AS the cell entry for
any given row in one column is the same length as the corresponding cell
entry for that same row in the other column (which is what your sample data
showed); just change the Column1 and Column2 constants to the column letters
corresponding to your actual data and change the worksheet reference in the
With statement to the worksheet name where your data is located...

Sub HighlightDifferences()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Const Column1 As String = "A"
Const Column2 As String = "B"
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, Column1).End(xlUp).Row
For X = 1 To LastRow
.Cells(X, Column1).Characters.Font.ColorIndex = xlColorIndexAutomatic
.Cells(X, Column2).Characters.Font.ColorIndex = xlColorIndexAutomatic
For Z = 1 To Len(.Cells(X, Column1).Value)
If .Cells(X, Column1).Characters(Z, 1).Text < _
.Cells(X, Column2).Characters(Z, 1).Text Then
.Cells(X, Column1).Characters(Z, 1).Font.ColorIndex = 3
.Cells(X, Column2).Characters(Z, 1).Font.ColorIndex = 3
End If
Next
Next
End With
End Sub

--
Rick (MVP - Excel)


"aileen" wrote in message
...
I have two strings of data that I compare with a vlookup and then I kick
out
the data that doesn't match into two different columns. I am wondering if
it
is possible to have the strings of data highlight just the portion of the
string that doesn't match.
For example:
2008JXA800OCT0250 2008JXA800JAN0250

2008JXA850OCT0301 2008JXA850JAN0306

2009JXA900OCT07 2008JXA900JAN07
The 1st string would highlight the OCT and the 2nd string across from it
would highlight the JAN. While the next row the 1st string would
highlight
the 1 and the 2nd string across from it would highlight the 6 and in the
third row the 1st string would highlight the 9 (from the 2009) and the 2nd
string across from it would highlight the 8(from the 2008).

Any help is always appreciated. Thanks!

unmatched data string
 

Thanks so much, it worked perfectly.

"Rick Rothstein" wrote:

The following macro should do what you want AS LONG AS the cell entry for
any given row in one column is the same length as the corresponding cell
entry for that same row in the other column (which is what your sample data
showed); just change the Column1 and Column2 constants to the column letters
corresponding to your actual data and change the worksheet reference in the
With statement to the worksheet name where your data is located...

Sub HighlightDifferences()
Dim X As Long
Dim Z As Long
Dim LastRow As Long
Const Column1 As String = "A"
Const Column2 As String = "B"
With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, Column1).End(xlUp).Row
For X = 1 To LastRow
.Cells(X, Column1).Characters.Font.ColorIndex = xlColorIndexAutomatic
.Cells(X, Column2).Characters.Font.ColorIndex = xlColorIndexAutomatic
For Z = 1 To Len(.Cells(X, Column1).Value)
If .Cells(X, Column1).Characters(Z, 1).Text < _
.Cells(X, Column2).Characters(Z, 1).Text Then
.Cells(X, Column1).Characters(Z, 1).Font.ColorIndex = 3
.Cells(X, Column2).Characters(Z, 1).Font.ColorIndex = 3
End If
Next
Next
End With
End Sub

--
Rick (MVP - Excel)


"aileen" wrote in message
...
I have two strings of data that I compare with a vlookup and then I kick
out
the data that doesn't match into two different columns. I am wondering if
it
is possible to have the strings of data highlight just the portion of the
string that doesn't match.
For example:
2008JXA800OCT0250 2008JXA800JAN0250

2008JXA850OCT0301 2008JXA850JAN0306

2009JXA900OCT07 2008JXA900JAN07
The 1st string would highlight the OCT and the 2nd string across from it
would highlight the JAN. While the next row the 1st string would
highlight
the 1 and the 2nd string across from it would highlight the 6 and in the
third row the 1st string would highlight the 9 (from the 2009) and the 2nd
string across from it would highlight the 8(from the 2008).

Any help is always appreciated. Thanks!