Row number of a specific value

[[email protected]]

Hi,
How do I get the row number of a specific cell?
Lets suppose I have a column colour

Colour
black
blue
green

and I'd like to get the row number of the cell containing the value
blue.
How can I do it?

Thanks
Faby

[excelent]

This one looks in column A

Sub color()

For r = 1 To 10
If Cells(r, 1).Interior.ColorIndex = 5 Then
MsgBox ("Row ") & r
End If
Next

End Sub


"[email protected]" skrev:

> Hi,
> How do I get the row number of a specific cell?
> Lets suppose I have a column colour
>
> Colour
> black
> blue
> green
>
> and I'd like to get the row number of the cell containing the value
> blue.
> How can I do it?
>
> Thanks
> Faby
>
>

[Bob Phillips]

=MATCH("blue",A1:A20,0)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<[email protected]> wrote in message
news:[email protected]...
> Hi,
> How do I get the row number of a specific cell?
> Lets suppose I have a column colour
>
> Colour
> black
> blue
> green
>
> and I'd like to get the row number of the cell containing the value
> blue.
> How can I do it?
>
> Thanks
> Faby
>

[RB Smissaert]

If you have your colours + the field name in A1 to A4 then this will do:

Sub test()

On Error GoTo ERROROUT

MsgBox Range(Cells(1), Cells(4, 1)).Find(What:="blue", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False).Row, , _
"row number of the cell with
blue"
Exit Sub
ERROROUT:

MsgBox "No cell found with blue in that range"

End Sub


RBS

<[email protected]> wrote in message
news:[email protected]...
> Hi,
> How do I get the row number of a specific cell?
> Lets suppose I have a column colour
>
> Colour
> black
> blue
> green
>
> and I'd like to get the row number of the cell containing the value
> blue.
> How can I do it?
>
> Thanks
> Faby
>

[Faby]

Hi! Thank you very much for all your answers!! It's all working fine
now.
Faby