Highlight Differences Between Two Columns

[Excel_Trainee]

Hello Everyone! I am trying to run the above macros to highlight the difference between two columns and i am getting the error at:

If Range("wordMatch") Then (LINE 16 in the above code)

The error: Run-time error '1004'
Method 'Range' of object'_Global' failed

 

[Excel_Trainee]

Here is the macro i am using-----

Sub highlightDiffs()
'
' highlightDiffs Macro
'
Dim cell1 As Range, cell2 As Range, i As Long
Dim j As Long, k As Long, length As Long, word1 As String, word2 As String

resetColors

i = 1
For Each cell1 In Range("list1")
Set cell2 = Range("list2").Cells(i)
If Not cell1.Value2 = cell2.Value2 Then
'both cells don't match. Find the first word / character that doesn't match
length = Len(cell1.Value2)
If Range("wordMatch") Then
'match words
j = 1
k = 1
Do
word1 = nextWord(cell1.Value2, j)
word2 = nextWord(cell2.Value2, k)
If Not word1 = word2 Then
With cell2.Characters(k, Len(word2)).Font
.Color = -16776961
End With
End If
j = j + Len(word1) + 1
k = k + Len(word2) + 1
Loop While j <= length
If k <= Len(cell2.Value2) Then
With cell2.Characters(k, Len(cell2.Value2) - k + 1).Font
.Color = -16776961
End With
End If
Else
'match letters

For j = 1 To length
If Not cell1.Characters(j, 1).Text = cell2.Characters(j, 1).Text _
Then Exit For
Next j
If j <= Len(cell2.Value2) Then
With cell2.Characters(j, Len(cell2.Value2) - j + 1).Font
.Color = -16776961
End With
End If
End If

End If
i = i + 1
Next cell1

End Sub

Sub resetColors()
'
' resetColors Macro
'
With Range("list2").Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
End Sub

Function nextWord(fromThis As String, startHere As Long) As String

' returns the next word starting from startHere
' ending with a delimeter from ., ?!"';

Dim i As Long
Dim delim As String

delim = " .,?!"""
startHere = If(delim Like "*" & Mid(fromThis, startHere, 1) & "*", startHere + 1, startHere)

For i = startHere To Len(fromThis)
If delim Like "*" & Mid(fromThis, i, 1) & "*" Then Exit For
Next i
nextWord = Trim(Mid(fromThis, startHere, i - startHere))
End Function

 

[Joe4]

How/where is "wordMatch" defined?
What is the size of the range, and what possible values occur in that range?

 

[Excel_Trainee]

Hello Joe4!

Thank you looking at the issue. Unfortunately, I do not know how to read macros (my first time using it, I found this macro online) so I do not know where “wordMatch” is defined. I have attached the macro above.

This is what I can say: the range size can be anywhere from a few lines to a paragraph or two. It could also be bullet points. These are mostly alphabets although some might contain numbers.

Does it help answer your questions?Or am I making no sense?

 

[Joe4]

It looks to me like the macro is using pre-existing named ranges, like "wordMatch".
If you have not created that manually, or defined that elsewhere in your code, that would certainly be problematic. You would be trying to reference something that doesn't exist!

I think you should take a different approach to this problem. Why don't you state what your problem is, exactly what you are trying to do (in detail, like the structure of your data)? You can include images if that helps in your explanation (see: XL2BB - Excel Range to BBCode). And if you found some code online that you trying to incorporate, include a link to that, so we can see exactly how it was originally intended to be used.

 

[Excel_Trainee]

It looks to me like the macro is using pre-existing named ranges, like "wordMatch".
If you have not created that manually, or defined that elsewhere in your code, that would certainly be problematic. You would be trying to reference something that doesn't exist!

I think you should take a different approach to this problem. Why don't you state what your problem is, exactly what you are trying to do (in detail, like the structure of your data)? You can include images if that helps in your explanation (see: XL2BB - Excel Range to BBCode). And if you found some code online that you trying to incorporate, include a link to that, so we can see exactly how it was originally intended to be used.

Hello Joe4,
after doing some more research and learning more about VBA, I was able to figure out the code. Having said that, this code currently has a potential issue. I’m cases where there are ‘ or “” in let says row number 2 and 3 (suppose we have 3 rows of data), this code does not highlight that being different between the two columns that are being compared. How can I rectify this issue?
Eg.
column A Column B
This is a test This is a test
Hello friend. Hello welcome friend’s friend
My name is “Ram” My name is Adam

 

[Joe4]

Can you post your current version of your code?