Glaswegian
06-09-2005, 04:00 AM
This is making a bad day even worse!:banghead:
I'm using this code to run a quick match against two columns - it works great and highlights differences, which is all I need.
But - when I run this code from the macro dialogue box, it asks for both ranges twice. When I step through the code in the VB Editor it only asks once.
Sub FindDuplicates() 'matches against 2 cols
Dim Rng1 As Range 'matches first cell in first range against each cell in second 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)
If compRng Is Nothing Then Exit Sub
For Each Rng1 In origRng
bMatch = False
For Each Rng2 In compRng
If Rng1 = Rng2 Then
bMatch = True
Rng2.Interior.ColorIndex = 4
End If
Next Rng2
If bMatch = False Then
Rng1.Interior.ColorIndex = 3
End If
Next Rng1
End Sub
I can't see any obvious reason why this should happen. :dunno
Am I missing something obvious here?
Regards
I'm using this code to run a quick match against two columns - it works great and highlights differences, which is all I need.
But - when I run this code from the macro dialogue box, it asks for both ranges twice. When I step through the code in the VB Editor it only asks once.
Sub FindDuplicates() 'matches against 2 cols
Dim Rng1 As Range 'matches first cell in first range against each cell in second 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)
If compRng Is Nothing Then Exit Sub
For Each Rng1 In origRng
bMatch = False
For Each Rng2 In compRng
If Rng1 = Rng2 Then
bMatch = True
Rng2.Interior.ColorIndex = 4
End If
Next Rng2
If bMatch = False Then
Rng1.Interior.ColorIndex = 3
End If
Next Rng1
End Sub
I can't see any obvious reason why this should happen. :dunno
Am I missing something obvious here?
Regards