Search cells in a column and return yes/no if they are present in named range

[Felicity Geronimo]

Hi,

Please help, I'm trying to select a particular column and then search
each cell in that column to see if any of the values exist in a named
range, i want to do this for each sheet in the workbook.

For example: I have 4 worksheets in by workbook, 3 of which all
contain Site field (column B). The 4th sheet contains a named range
("SITE") I want to check that each site in column B (all sheets)exists
in the named range on the 4th sheet.

Is this possible, thank you in advance

Felicity

 

[Dave D-C]

Please help, I'm trying to select a particular column and then search
each cell in that column to see if any of the values exist in a named
range, i want to do this for each sheet in the workbook.

For example: I have 4 worksheets in by workbook, 3 of which all
contain Site field (column B). The 4th sheet contains a named range
("SITE") I want to check that each site in column B (all sheets)exists
in the named range on the 4th sheet.

This shoud be a start:
Sub MacName()
Call SubName("Sheet1")
Call SubName("Sheet2")
Call SubName("Sheet3")
End Sub
Sub SubName(WSName$)
Dim zCell1 As Range, zCell2 As Range, s$
For Each zCell1 In Worksheets(WSName).Range("B:B")
s = zCell1.Value ' site
If s <> "" Then
For Each zCell2 In Range("SITE")
If zCell2.Value = s Then
MsgBox "Match: " & s
GoTo NextzCell1 ' sorry about the goto
End If
Next zCell2
MsgBox "Nomatch: " & s
End If
NextzCell1:
Next zCell1
End Sub