Comparing 2 dynamic ranges for matching names

[Daminc]

I've spent the last 5-6 hours trying to solve this problem with no joy

I have one named range called Highlighted_names

Please Login or Register  to view this content.

and another called Downloaded_list

Please Login or Register  to view this content.

These are on 2 different sheets.

My hope is have a Marco that checks the cells in the first range and compares it to the second range and Selects any matches.

I then intend to highlight the rows in which these matches occur.

Any help would be appreciated

[Tom Ogilvy]

Dim rng1 as Range, rng2 as Range
Dim res as Variant, cell as Range
set rng1 = Range("Highlighted_Names")
set rng2 = Range("DownLoadedList")
rng1.Interior.colorIndex = xlNone
for each cell in rng1
res = Application.Match(cell,rng2,0)
if not iserror(res) then
cell.Interior.colorIndex = 3
end if
Next


--
Regards,
Tom Ogilvy

"Daminc" <[email protected]> wrote in
message news:[email protected]...
>
> I've spent the last 5-6 hours trying to solve this problem with no joy
>
> I have one named range called Highlighted_names
>
>
> Code:
> --------------------
> =OFFSET(Highlighted_data!$A$3,0,0,COUNTA(Highlighted_data!$A:$A),1)
> --------------------
>
>
> and another called Downloaded_list
>
>
> Code:
> --------------------
> =OFFSET(Download_sheet!$B$2,0,0,COUNTA(Download_sheet!$B:$B),1)
> --------------------
>
>
> These are on 2 different sheets.
>
> My hope is have a Marco that checks the cells in the first range and
> compares it to the second range and Selects any matches.
>
> I then intend to highlight the rows in which these matches occur.
>
> Any help would be appreciated
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=473778
>

[Jim Thomlinson]

Here is an adaptation of some code that I already have. It looks for a match
in two different ranges. It creates a new sheet listing the matched items.
You should be able to adapt this to your needs... For this code to work you
need to reference "Microsoft Scripting Runtime" (in the VB editor Tools ->
References...)

Sub Matched()
Dim rngRange1 As Range
Dim rngRange2 As Range
Dim rngCurrent As Range
Dim Dic1 As Scripting.Dictionary 'Dictionary Object
Dim Dic2 As Scripting.Dictionary 'Dictionary Object
Dim varMatched As Variant 'Array of unmatched items
Dim wksNew As Worksheet
Dim lngCounter As Long

Set rngRange1 = Sheets("Sheet1").Range("A1:A100") 'Change This
Set rngRange2 = Sheets("Sheet2").Range("A1:A50") 'Change This

Set Dic1 = CreateDictionary(rngRange1)
Set Dic2 = CreateDictionary(rngRange2)
varMatched = MatchedArray(Dic1, Dic2)
If IsArray(varMatched) Then
Set wksNew = Sheets.Add
With wksNew
.Range("A1").Value = "Matched Items"
Set rngCurrent = .Range("A2")
For lngCounter = LBound(varMatched) To UBound(varMatched)
rngCurrent.Value = varMatched(lngCounter)
Set rngCurrent = rngCurrent.Offset(1, 0)
Next lngCounter

End With
Else
MsgBox "No Matching Items", vbOKOnly, "No Matches"
End If

End Sub

Private Function CreateDictionary(ByVal Target As Range) As
Scripting.Dictionary
Dim rngCurrent As Range
Dim dic As Scripting.Dictionary 'Dictionary Object

Set dic = New Scripting.Dictionary
For Each rngCurrent In Target
If Not dic.Exists(rngCurrent.Value) And rngCurrent.Value <> Empty
Then 'Check the key
dic.Add rngCurrent.Value, rngCurrent.Value 'Add the item if
unique
End If
Next rngCurrent

Set CreateDictionary = dic
End Function

Private Function MatchedArray(ByVal Dic1 As Scripting.Dictionary, _
ByVal Dic2 As Scripting.Dictionary) As Variant
Dim dicItem As Variant
Dim aryMatched() As String
Dim lngCounter As Long

lngCounter = 0
For Each dicItem In Dic1
If Dic2.Exists(dicItem) Then 'Check the key
ReDim Preserve aryMatched(lngCounter)
aryMatched(lngCounter) = dicItem
lngCounter = lngCounter + 1
End If
Next dicItem

If lngCounter = 0 Then
MatchedArray = Empty
Else
MatchedArray = aryMatched
End If
End Function
--
HTH...

Jim Thomlinson


"Daminc" wrote:

>
> I've spent the last 5-6 hours trying to solve this problem with no joy
>
> I have one named range called Highlighted_names
>
>
> Code:
> --------------------
> =OFFSET(Highlighted_data!$A$3,0,0,COUNTA(Highlighted_data!$A:$A),1)
> --------------------
>
>
> and another called Downloaded_list
>
>
> Code:
> --------------------
> =OFFSET(Download_sheet!$B$2,0,0,COUNTA(Download_sheet!$B:$B),1)
> --------------------
>
>
> These are on 2 different sheets.
>
> My hope is have a Marco that checks the cells in the first range and
> compares it to the second range and Selects any matches.
>
> I then intend to highlight the rows in which these matches occur.
>
> Any help would be appreciated
>
>
> --
> Daminc
> ------------------------------------------------------------------------
> Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
> View this thread: http://www.excelforum.com/showthread...hreadid=473778
>
>

[Daminc]

Cheers Tom, that code you gave helped a lot (it also gave me a better understanding of coding so that's a double bonus )

Jim, I'll keep that code around for now because it will be an education trying to decipher it with my meager knowledge. Still, when I do work it out I'll know I've reached the next level (I wonder if I'll get a medal or something )

Cheers for your help guys.