jonathynblythe
New member
- Joined
- Jul 5, 2011
- Messages
- 13
- Reaction score
- 0
- Points
- 0
Hello and thank you for letting me into this forum!
I have a code that I just recently had modified and it looks like this:
Now here's what I keep seeing when I check the numbers I want to check:
Now as you'll notice from the graphic above, the name Vicki Sikkink is highlighted red. The code posted above does that to show me that something is not matching with the other sheet... in this case, the name. All names with this code are compared through the phone numbers as their starting point.
Now here's the problem... this second sheet here is what it compared Vicki to:
As you can see from the first name on this sheet, the business "Intensive Hair Unit", it has the exact same phone number as Vicki (hence, this is her business). I didn't want the red highlight to highlight Vicki's name since as you can see from the bottom of the sheet, she is right down there also where her name matches what was on the first sheet (image1). What's happening is, this excel macro is assuming that since there is so much as one difference on that sheet with that number, the whole thing is wrong... where it doesn't even consider the fact that there is one that matches exactly.
How do I modify this code so that if it sees so much as one perfect match, then there will be no red highlight? It can disregard all the other differences if one matches. I'd like to add whatever the answer is to this to Leith's code that he gave me here too!
Thank you in advance!
JB
I have a code that I just recently had modified and it looks like this:
Code:
Sub FindDuplicates2() 'matches against 2 cols
Dim rng1 As Range
Dim rng2 As Range
Dim bMatch As Boolean
Dim origRng As Range
Dim compRng As Range
On Error Resume Next
Set origRng = Application.InputBox("Choose the first range", "Range 1", Type:=8)
If origRng Is Nothing Then Exit Sub
Set compRng = Application.InputBox("Choose the second range", "Range 2", Type:=8)
'matches first cell in first range against each cell in second range
'ranges do not need to be equal size
'if there is a match then cell in second range turns green
'if there is not a match then cell in first range turns red
For Each rng1 In origRng
bMatch = False
For Each rng2 In compRng
If rng2.Text Like "*" & rng1.Text & "*" Then
bMatch = True
rng2.Interior.ColorIndex = 0
If rng2.Offset(0, -4).Value <> rng1.Offset(0, -4).Value Then
rng1.Offset(0, -4).Interior.ColorIndex = 3
End If
End If
Next rng2
If bMatch = False Then
rng1.Interior.ColorIndex = 41
End If
Next rng1
For Each rng1 In origRng
bMatch = False
For Each rng2 In compRng
If rng2.Text Like "*" & rng1.Text & "*" Then
bMatch = True
rng2.Interior.ColorIndex = 0
If rng2.Offset(0, -5).Value <> rng1.Offset(0, -5).Value Then
rng1.Offset(0, -5).Interior.ColorIndex = 3
End If
End If
Next rng2
If bMatch = False Then
rng1.Interior.ColorIndex = 41
End If
Next rng1
For Each rng1 In origRng
bMatch = False
For Each rng2 In compRng
If rng2.Text Like "*" & rng1.Text & "*" Then
bMatch = True
rng2.Interior.ColorIndex = 0
If rng2.Offset(0, -3).Value <> rng1.Offset(0, -3).Value Then
rng1.Offset(0, -3).Interior.ColorIndex = 3
End If
End If
Next rng2
If bMatch = False Then
rng1.Interior.ColorIndex = 41
End If
Next rng1
For Each rng1 In origRng
bMatch = False
For Each rng2 In compRng
If rng2.Text Like "*" & rng1.Text & "*" Then
bMatch = True
rng2.Interior.ColorIndex = 0
If rng2.Offset(0, -1).Value <> rng1.Offset(0, -1).Value Then
rng1.Offset(0, -1).Interior.ColorIndex = 3
End If
End If
Next rng2
If bMatch = False Then
rng1.Interior.ColorIndex = 41
End If
Next rng1
End Sub
Now here's what I keep seeing when I check the numbers I want to check:
Now as you'll notice from the graphic above, the name Vicki Sikkink is highlighted red. The code posted above does that to show me that something is not matching with the other sheet... in this case, the name. All names with this code are compared through the phone numbers as their starting point.
Now here's the problem... this second sheet here is what it compared Vicki to:
As you can see from the first name on this sheet, the business "Intensive Hair Unit", it has the exact same phone number as Vicki (hence, this is her business). I didn't want the red highlight to highlight Vicki's name since as you can see from the bottom of the sheet, she is right down there also where her name matches what was on the first sheet (image1). What's happening is, this excel macro is assuming that since there is so much as one difference on that sheet with that number, the whole thing is wrong... where it doesn't even consider the fact that there is one that matches exactly.
How do I modify this code so that if it sees so much as one perfect match, then there will be no red highlight? It can disregard all the other differences if one matches. I'd like to add whatever the answer is to this to Leith's code that he gave me here too!
Thank you in advance!
JB