PDA

View Full Version : Solved: Active cell highlights row



Asus
02-28-2009, 08:00 AM
:banghead:
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
02-28-2009, 08:05 AM
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
02-28-2009, 08:22 AM
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. :dunno . I did not define the action?
Thanks

lucas
02-28-2009, 09:03 AM
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
02-28-2009, 09:33 AM
Sub FindSN()
Dim cell As Range

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


replace 1234 with your target SN

Asus
02-28-2009, 10:02 AM
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
02-28-2009, 10:05 AM
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
02-28-2009, 10:27 AM
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
02-28-2009, 10:50 AM
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

mdmackillop
02-28-2009, 10:58 AM
This sample colours the row until a cell in another row is selected.

Asus
02-28-2009, 11:58 AM
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
02-28-2009, 01:01 PM
I'm not clear what you mean by move that button, but have a look at this

lucas
02-28-2009, 01:12 PM
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
02-28-2009, 01:32 PM
Excellent!!!
Thak you all of you.
I learned a lot of good things from this.

lucas
02-28-2009, 02:09 PM
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
02-28-2009, 02:21 PM
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.:thumb

mdmackillop
02-28-2009, 03:28 PM
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!:think: