A problem with duplication in Excel's macro

J

jonathynblythe

New Member
Joined
Apr 14, 2011
Messages
4
Hello,

You guys know me from a while back. Glaswegian gave me a code that I just recently had modified and it looks like this:

<STYLE>.alt2 font {font: 11px monospace !important;color: #333 !important;}</STYLE>

Code: 
[FONT=Verdana][COLOR=#333333][COLOR=#333333][FONT=Verdana]Sub FindDuplicates2() 'matches against 2 cols<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] Dim rng1 As Range<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] Dim rng2 As Range<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] Dim bMatch As Boolean<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] Dim origRng As Range<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] Dim compRng As Range<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]  <o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] On Error Resume Next<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] Set origRng = Application.InputBox("Choose the first range", "Range 1", Type:=8)<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]     If origRng Is Nothing Then Exit Sub<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] Set compRng = Application.InputBox("Choose the second range", "Range 2", Type:=8)<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] 'matches first cell in first range against each cell in second range<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] 'ranges do not need to be equal size<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] 'if there is a match then cell in second range turns green<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] 'if there is not a  match then cell in first range turns red<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]     For Each rng1 In origRng<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]         bMatch = False<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]         For Each rng2 In compRng<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             If rng2.Text Like "*" & rng1.Text & "*" Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                 bMatch = True<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                 rng2.Interior.ColorIndex = 0<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                     If rng2.Offset(0, -4).Value <> rng1.Offset(0, -4).Value Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                         rng1.Offset(0, -4).Interior.ColorIndex = 3<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                     <o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]         Next rng2<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             If bMatch = False Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                 rng1.Interior.ColorIndex = 41<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]     Next rng1<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]      For Each rng1 In origRng<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]         bMatch = False<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]         For Each rng2 In compRng<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             If rng2.Text Like "*" & rng1.Text & "*" Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                 bMatch = True<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                 rng2.Interior.ColorIndex = 0<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                     If rng2.Offset(0, -5).Value <> rng1.Offset(0, -5).Value Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                         rng1.Offset(0, -5).Interior.ColorIndex = 3<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                     End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]         Next rng2<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             If bMatch = False Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                 rng1.Interior.ColorIndex = 41<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]     Next rng1<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]      For Each rng1 In origRng<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]         bMatch = False<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]         For Each rng2 In compRng<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             If rng2.Text Like "*" & rng1.Text & "*" Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                 bMatch = True<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                 rng2.Interior.ColorIndex = 0<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                     If rng2.Offset(0, -3).Value <> rng1.Offset(0, -3).Value Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                         rng1.Offset(0, -3).Interior.ColorIndex = 3<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                     End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]         Next rng2<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             If bMatch = False Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                 rng1.Interior.ColorIndex = 41<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]     Next rng1<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]      For Each rng1 In origRng<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]         bMatch = False<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]         For Each rng2 In compRng<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             If rng2.Text Like "*" & rng1.Text & "*" Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                 bMatch = True<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                 rng2.Interior.ColorIndex = 0<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                     If rng2.Offset(0, -1).Value <> rng1.Offset(0, -1).Value Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                         rng1.Offset(0, -1).Interior.ColorIndex = 3<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                     End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]         Next rng2<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             If bMatch = False Then<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]                 rng1.Interior.ColorIndex = 41<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]             End If<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]     Next rng1<o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana]     <o:p></o:p>[/FONT][/COLOR]
[COLOR=#333333][FONT=Verdana] End Sub[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
 
[/COLOR][/FONT]




<!-- BEGIN TEMPLATE: bbcode_code --><STYLE>.alt2 font {font: 11px monospace !important;color: #333 !important;}</STYLE><!-- END TEMPLATE: bbcode_code -->Now here's what I keep seeing when I check the numbers I want to check: the name (customer) 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 (customer) to: On this sheet, a business has the exact same phone number as (customer, hence, this is her business). I didn't want the red highlight to highlight Vicki's name from the bottom of the sheet, she is right down there also where her name matches what was on the first sheet. 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
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You must log in or register to reply here.

Similar threads

cpg84
  • Question Question
Merging and unmerging cells based on another cell's value
Replies
2
Views
299
cpg84
cpg84
L
  • Solved
Combining Worksheet Changes
Replies
3
Views
304
Joe4
Joe4
S
  • Solved
Copy hyperlinks from one table column to another. The columns are identical
Replies
2
Views
415
sspatriots
S
N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
662
myall_blues
myall_blues
H
  • Question Question
Add a criteria to VBA Code
Replies
8
Views
370
hrayani
H

Forum statistics

Threads
1,224,506
Messages
6,179,159
Members
452,892
Latest member
yadavagiri

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top