If I enter a number on sheet 1 I want to know if it exist on any of the sheets in the array.

As is, if I enter 2468 on sheet 1 it runs through the array sheets but does not activate the number 2468 on sheet 4 which I am using for the test. The other sheets have no matching number in my test.

I can un-comment the MsgBox and it verifies "CheckNum" as the sheets are tested, but nothing happens on sheet 4 where there is indeed a true match to CheckNum.

If I run the Macro 8 sub in sheet 4 module it selects the 2468 as I would expect from both it and the change event.

If I try this line I get an yellow highlight error. Doesn't want the . in front of UsedRange.

.UsedRange.Find(What:=CheckNum).Activate

My ultimate goal is to produce a MsgBox on sheet 1 telling the user that the number exists on ...Sheet 3 $D:$6, for example or "Not Found".

Gotta get past this elusive Find code first.

Thanks,
Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim CheckNum As Long
Dim varSheets As Variant
Dim i As Long

CheckNum = Target.Value

varSheets = Array("Sheet2", "Sheet3", "Sheet4")

For i = LBound(varSheets) To UBound(varSheets)
With Sheets(varSheets(i))
'MsgBox CheckNum
UsedRange.Find(What:=CheckNum).Activate

End With
Next
End Sub



Option Explicit

Sub Macro8()
Dim CheckNum As Long
CheckNum = 2468
With Sheets("Sheet4")
UsedRange.Find(What:=CheckNum).Activate
End With
End Sub