Hi Norman

I have got back to this and have adapted it to find the specified number and
it ends up selecting (and colouring) the cell with:

With RngFound
MsgBox .Address(0, 0)
.Interior.ColorIndex = 6
.Select
End With

I don't want to select the cell - I need the row number as a pointer to the
next part of the subroutine but I cannot find how to get the row number out
of this.

RowReqd = RtnFound.Row
No, that's no good!

Please...

Francis Hookham


"Norman Jones" wrote in message
...
Hi Francis,

'---------------
This problem just shows up how little I know - I do not know how to make
your suggestion work. It's late and I'm old and tired and have tried
unsuccessfully to set out below a Sub which would crudley do what I am
trying to do - it does not work but it might show what I want - please
'---------------

Try the following version:

'=============
Public Sub Tester001()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range
Dim RngFound As Range
Dim iLastRow As Long
Dim Res As String

Set WB = Workbooks("MyBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Specs")
iLastRow = SH.Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = SH.Range("A2:A" & iLastRow)

Res = InputBox("Find which number?")

If Res = vbNullString Then
'Nothing to find,
Exit Sub
End If

Set RngFound = Rng.Find(What:=Res, _
After:=Rng.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not RngFound Is Nothing Then
'do something, e.g:
With RngFound
MsgBox .Address(0, 0)
.Interior.ColorIndex = 6
.Select
End With
Else
MsgBox Prompt:="The string " _
& Res & " was not found", _
Buttons:=vbCritical, _
Title:="Not Found!"
End If

End Sub
'<<=============


---
Regards,
Norman