This adds the cell address to sz if it's value matches the criteria,
only if the address is not already there. Var sz holds the addresses so
they can be formatted correctly to pass as a valid list for Range().

--
Garry

Okay, makes some sense to me now. Thanks.

Here is a slightly modified version I intend to pass on unless there is already a solution provided. (Been chasing this for most of a day.)

Howard

Private Sub Worksheet_Change(ByVal Target As Range)
'/ by Garry MS Public Prog.

Application.EnableEvents = False

If IsNumeric(Target.Value) = False Then
Range("F2").Select
MsgBox "Must be number!"
Range("F1").Activate
Application.EnableEvents = True
Exit Sub
End If

If Intersect(Target, Range("F1")) Is Nothing Then Exit Sub

Dim aNum As Long
Dim n&, k&, sz$

aNum = Range("F1")

ReDim vArray(1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange, aNum))
For n = LBound(vArray) To UBound(vArray)
With ActiveSheet.UsedRange
For k = 1 To .Cells.Count
If .Cells(k) = aNum And InStr(sz, .Cells(k).Address) = 0 Then
sz = sz & "," & .Cells(k).Address: Exit For
End If
Next 'k
End With 'ActiveSheet.UsedRange
Next 'n
'sz = Replace(Mid(sz, 2), ",", ", ")
Range(Replace(Mid(sz, 2), ",", ", ")).Select

Application.EnableEvents = True

End Sub