Solved: Active cell highlights row

[Asus]

I am pretty green at this stuff. There is a wealth of information here. I tried to find what i was looking for but there is just so much and I may ot be asking it the correct way.
I use excel 2002 in the office
I have a inventory sheet "Edmonton" I use that has the serial numbers in column A.
I would like to look for a SN and when found it would higlight the row (like when you click the row number beside the SN in column A)
Ideally I would like to use a button (instead of the find (crtl+f))

Thanks in Advance

Knowledge is true power but only when it is shared

[Foxtrot]

I've used this code in another project.

If Cell.Value = "y" Then Cell.EntireRow.Select

I'm pretty basic too, but am trying to get my head around this stuff.

[Asus]

I put that code you posted in on the sheet VBAproject and I get no results? I tried to make sense of the code but get tripped up real fast. Can you explain to me if I put it in right? (I just did a copy and past) Are there vaiables I should be looking at. I know I did something wrong. . I did not define the action?
Thanks

[lucas]

How do you want to look for them? would a listbox that shows the contents of column A work? You just select the item and it's row is highlighted or do you actually want to search column A?

This is fairly easy once we get a few things organized.

[Bob Phillips]

[vba]


Sub FindSN()
Dim cell As Range

Set cell = Columns(1).Find("1234")
If Not cell Is Nothing Then cell.EntireRow.Select
End Sub
[/vba]

replace 1234 with your target SN

[Asus]

How would I make it ask me for the SN rather than editing the code for each search? In my simplistic thinking I was (in my mind) that if I ran a macro and or did a FIND and entered the serial number (that is in column A) it would go to that cell and select (highlight) that row. I was thinking that there must be a way to select the row or by code or click the row number and the row highlights. Like I said I am very new at this and it seems logical to do but I have no way of making Excel understand what I am asking (well perhaps I have a hard time explaining it to myself as well).

Thanks for your time

Always a pleasure to learn something

[Bob Phillips]

So what do you want to do? A macro to ask for the SN and then find it, or some code that will select the whole row of a selected cell?

[Asus]

I have a spread sheet which I monitor the inventory with. I can place a unit in stock or recycle. Not wishing to make a mistake. I would like an automated way (macro?) that when I do a find (of a serial number that is in coulmn A) that the row would highlight (thereby making sure I am on the correct line when I change the status of that unt (ie stock or recycle). Yes I know I can do a FIND and it will find the serial number and yes I can just clikc the row number besdie the serial number to highlight the row. I was just looking for an automated way of doing that. I like to see what Excel can do and how it is done.
let's say:
A macro (I can make a button) that asks me to input a serial number. The macro then checks for that exact entire serial # then high lights that row so I do not make an errors on that serial number.

does that make any sense?

I appreciate your time and effort

[Bob Phillips]

[vba]

Sub FindSN()
Dim cell As Range
Dim SN As String

SN = InputBox("Which serial number?")
If SN <> "" Then

Set cell = Columns(1).Find("1234")
If Not cell Is Nothing Then cell.EntireRow.Select
End If
End Sub
[/vba]

[mdmackillop]

This sample colours the row until a cell in another row is selected.

[Asus]

Yes I know from your post I need to give much more detail when asking for help so people can actually help me quicker. I will keep note of that, thanks.
Your example works great.
Mine does not in my sheet. Yes this goes back to me not giving enough detail.
On the sheet that I wish to use this it already has a a Private Sub CommandButton1_Click()
I attached my sheet. And how would one move that button?
Thanks

[mdmackillop]

I'm not clear what you mean by move that button, but have a look at this

[lucas]

Hi Malcolm, I had just added your code to his sheet but I kept the inputbox....since it's done I will post it so they can see both ways.....

[Asus]

Excellent!!!
Thak you all of you.
I learned a lot of good things from this.

[lucas]

You need to learn to add option explicit to the top of each of your standard module, sheet modules and thisworkbook module too. I notice you are not using it and you have variables not declared in your code.

It's good programming practice and will point out errors in your code.

[Asus]

Thank you I appreciate that piece of advice and will try and remind myself of that proper procedure. A big learning curve to learn Excel when you are older. Again most appreciated and accepted.

[mdmackillop]

A big learning curve to learn Excel when you are older.

You'll need to add a photo to your profile so we see if you qualify!