comparing two named ranges and if at least one match display a message

I have two ranges in the same workbook, one which is a static named range and one on a different worksheet that can have different dimensions. Each range contains values, not strings.

I want a macro that searches the two ranges and if one or more matches is found, displays a message in a specified cell in the worksheet with the unnamed range that says, "match found" .

thanks

Try this, renaming ranges to whatever suites your needs:

Sub Match()

For Each cell1 In Range("Named_Range_1")
    For Each cell2 In Range("D3:F7")
        If cell1.Value = cell2.Value Then
            MsgBox cell1.Address & " matches " & cell2.Address
        End If
    Next cell2
Next cell1

End Sub

Please put code between CODE tags. Cell1 and 2 should be dimmed as Ranges. Furthermore you made a little error in stating the range. See below in red.

Sub Match()

Dim cell1 As Range 'new line
Dim cell2 As Range'new line

For Each cell1 In Range("deal") 'errors here" For Each control variable must be Variant or Object
 For Each cell2 In Range("D10:D1000")
  If cell1.Value = cell2.Value Then
   MsgBox cell1.Address & " matches " & cell2.Address
  End If
 Next cell2
Next cell1

End Sub

Thanks. I tried the range datatype before I first replied, but it also bombs on first line referencing cell2. Sorry to be so stupid. I"m a beginner. does it have to do with not naming the active sheet?

The code works for named range and range on active sheet yet. Otherwise, instead use, e.g. for named range:

Thisworkbook.Worksheets("WorksheetName").Range("NamedRange")

Otherwise, what error do you get? Attach your workbook to the forum

thanks. It now works