underscroring/highlighting the row the "found" data is in

[marsmolly]

I use an excel spreadsheet constantly to look up item #s by using the Cntl + f key. I type in the item # and it finds it. Isn't there a way to underscore the row that the "found" item is in so you can follow across the spreadsheet without having to drag your finger across the screen? I could have sworn I used to use this function - but can not find this answer anywhere. Please help

 

[jasonb75]

point 1 I was not aware of, I will look into that.

point 2, this was something I had noticed, there is a far more simple solution that prevents this, may not be of use if you already have conditional formats applied to the range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveWorkbook.Names.Add Name:="findrow", RefersTo:=Rows(Target.Row)
End Sub

Then apply a conditional format to the range where the highlight should happen, using the formula

=ROW()=ROW(findrow)

Change the conditional fill, font, border, etc to highlight as desired.

 

[marsmolly]

I am sorry - I am not trying to get you to "do my homework" - but ... at home where I have a LITTLE freetime to mess with excel I have 2010. At my new job where they have 2000 - I am busy every minute and can't take time necessary to successfully test macros. There are several projects I want to accomplish just to make my job easier & this is one. I have been trying to learn VBA online (last time I was GREAT at excel was in 1998 or so - and either I forgot all that or it has all changed - who knows as my brain seems to shrink every year.
But with the new VBA you gave me - am I supposed to insert that in the other VBA code or put at the end of it - or do a whole new VBA macro?
Somehow I got your 1st one to work in 2010 - but now I'm lost. Any chance of getting one macro that does it all ? or a little better step by step instructions. ? =

I kind of feel good that I have a "new" problem to solve for all y'all - but I hate that I used to be good at excel and now I suck!

 

[jasonb75]

I forget how to do things after a few days so think after a few years I would have serious problems too I don't remember much about old versions of excel, but I would imagine that some of the things suggested might not have been possible back then.

The new code is intended to replace, delete the old code and start again.

To apply the conditional format for the code to work with, in excel 10 (I'm assuming it's the same set up as 07, note this is done in excel, not the VBA editor).

First select the range of cells on the worksheet that should highlight if a match is found, either with your mouse, or select the first item #, hold shift + ctrl, then press right arrow, down arrow.

Next press Alt H L N, in the window that shows, choose "use a formula to determine which cells to format"

Then in the formula box, enter =ROW()=ROW(findrow)

Click the format button under the formula box and change the font and fill options to make the rows stand out, click OK a couple of times to get back to excel, then try Ctrl F search to test it.

Hope that makes sense.