PDA

View Full Version : Comparing names in two worksheets for duplicates



jerrodroth
07-14-2017, 07:23 AM
Greetings...here is my issue. I have two worksheets where I am running the script below to check and find the duplicates between the two sheets. However, I cannot figure out how to ignore case differences. For example, I need 'smith, John' and 'Smith, john' to show as a match. Here is the script I have been using, but do not know how to modify:


Sub CompareRanges()
'Update 20130815
Dim WorkRng1 As Range, WorkRng2 As Range, Rng1 As Range, Rng2 As Range
Set WorkRng1 = Application.InputBox("Range A:",xTitleId, "", Type:=8)
Set WorkRng2 = Application.InputBox("Range B:", xTitleId, Type:=8)
For Each Rng1 In WorkRng1
rng1Value = Rng1.Value
For Each Rng2 In WorkRng2
If rng1Value = Rng2.Value Then
Rng1.Interior.Color = VBA.RGB(255, 0, 0)
Exit For
End If
Next
Next
End Sub

Thanks much!

JR

YasserKhalil
07-14-2017, 08:42 AM
Hello
Welcome to the forum
Please put the codes between code tags

try replace that line

If rng1Value = Rng2.Value Then
to

If UCase(rng1Value) = UCase(Rng2.Value) Then