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

 

[pedie]

I dont exactly understand what you're trying to acheive but..i think it wont be hard to do...ctrl + f is what you do...you can also add colour to hilight (with vba) the current found ....if not found msgbox etc....

But i think the simple ctrl+f works perfect...not matter where the answer may be when you hit find next it takes you there....

 

[jasonb75]

Welcome to the board!

I don't know of anything already in excel that does what you ask, see if this gives the desired result.

With the sheet where the search results would be found selected, right click the sheet tab then click "View Code"

Copy and paste the code below

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static oldrow
On Error Resume Next
    With Rows(oldrow).Interior
        .Pattern = xlNone
    End With
If Not Intersect(Target, Range("B2:B29")) Is Nothing Then
    With Rows(Target.Row).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
oldrow = Target.Row
End If
End Sub

Change the sample range B2:B29 to match the range on your sheet where results could be found, alternatively delete the red and blue text from the code to allow the highlight to run anywhere on the sheet.

Close the code window and test with ctrl f

 

[Peter_SSs]

Welcome to the MrExcel board!

It may not be exactly what you are looking for here because you perhaps have a whole column selected to do your find, but Shift+Spacebar selects the whole row(s) for the current selection.

 

[marsmolly]

Thank you all - I will try your suggestions.
(What I am trying to accomplish is - if you have a whole spreadsheet with percentages to be charged in columns to the right :

Item # Desc. etc. etc.. Cost 20% 30% 40% etc...

and you do a find on any part of the spreadsheet - word or item # - when it finds that cell, to see how much to charge for 30% you have to visually go across the line (or put your finger up and trace the line over). I will try your suggestions to see if one work where it will " _ _ _ _ _ _ " the row the item was found in (not highlight it permanently - just til next search)

 

[marsmolly]

YEAH!!! JASONB75 - That is EXACTLY what I wanted (even better really). Thank you! Check it out it really helps in spreadsheets!

 

[Peter_SSs]

YEAH!!! JASONB75 - That is EXACTLY what I wanted (even better really). Thank you! Check it out it really helps in spreadsheets!

Hmm, I'm not criticising the code (which highlights the first row of the current selection), but I must have misunderstood the requirement. With that code, if I am searching for something in B2:B29, I select B2:B29 (at this point row 2 is highlighted) and then do a Ctrl+F to find something that turns out to be in say B9. B9 becomes the active cell but when the 'Find' dialog is closed I still have row 2 highlighted, not row 9. What have I missed?

 

[jasonb75]

Peter, it only appears to work correctly if a single cell is selected, allowing the 'find' dialogue to search the whole sheet.

I used intersect to specify the 'find' result array so that any results outside of this range don't highlight, but I didn't allow for a specific range being selected prior to the search, doing this causes the highlight to lock to the first row of the selection.

Looking for a workaround all I'm seeing so far is an inputbox to search a named range.

 

[Peter_SSs]

Peter, it only appears to work correctly if a single cell is selected, allowing the 'find' dialogue to search the whole sheet.

And that is probably what the OP is doing - and what I had mis-interpreted.

 

[marsmolly]

Jason- your code worked perfectly for my 2010 excel. Then I copied it to my excel at work (2000) and the problems I encounter are: (I did make sure the security for macros was on Medium)

1) with 2010 you do not have to exit the Cntl + F search box for the "found" data row to highlight but in 2000 you have to exit the box. This is a pain because I constantly search for the next item.
2) in both 2010 & 2000: after the search highlights the row - the original color is gone and the color goes back to white. Needs to revert to previous... so...
columns:
item # description cost $ 20% 30% 40%
red yel grn blue

after it finds the selection it is overwriting row back to just no fill.
Thanks for any help!!