#1
|
|||
|
|||
Highlighting Excel FIND Results
Is there a way to have EXCEL highlight the current value found when looking at FIND All?
|
#2
|
|||
|
|||
I have never had much luck with the find all since usually I am looking in thousands of cells of data. However the code below will highlight any cells on the worksheet orange if there is an exact match and the yellow if there is a partial match. Be sure to save and backup your workbook before running.
Code:
Sub HighlightSearch() Dim rng As Range, c As Variant, SearchString As String SearchString = InputBox("Search the used range for?") If SearchString = "" Then End Set rng = ActiveSheet.UsedRange For Each c In rng If InStr(1, UCase(c.Value), UCase(SearchString)) Then c.Interior.ColorIndex = 6 'highlights yellow End If If UCase(c.Value) = UCase(SearchString) Then c.Interior.ColorIndex = 45 'highlights orange End If Next c End Sub |
#3
|
|||
|
|||
Highlighting of FIND All
Thank you for your reply. I was seeking something more user friendly. I have never used sub-routines.
Is this code easily inserted into Excel? and where? Thanks. |
#4
|
|||
|
|||
Like you, I seek the more user-friendly approaches. Here is my method: Select the range you wish to search, Select Home, Find & Select, Find, then enter your criteria. then Find All. The bottom of the Find & Replace dialog box will list all the cells that match (it may look like only 2 or 3 items, but there is a scroll bar on the right that will help display many more lines). Click on the first item, hold the Shift key, slide the scroll bar to the bottom, and still holding the Shift key, click on the last item. You have now selected all the matching cells. (Don't click anywhere else or you will lose your selection)
Now, select your highlight button (or "Theme Colors") and click on a highlight color. All your selected cells are now highlighted. |
#5
|
|||
|
|||
Nice technique jmhultin. I would certainly use something like this except all the workbooks I deal with have so many rows and columns of data that the find all would return 500 + results. and then I would not be able to select anything else on my worksheet until I found what I was looking for. I did not intend for my solution to be difficult. A lot of people will actually try to make you code it yourself but I will write it for you so you can just copy and paste it.
It is an extremely simple process to use any code after it is written. Complete these steps. Complete these steps. 1. open your workbook (until you are comfortable editing in the VBA editor you should only have 1 workbook open) 2. Press Alt + F11 to open up the editor. 3. On the Menu click Insert>Module 4. In the new module that comes up, copy and paste the entire code from Sub to End Sub 5. Close the VBA editor or run the code from the editor by pressing the play button while your text cursor is somewhere in the code. Again be sure your workbook is backed up before running because you cannot undo a macro. Let me know how that works out if you have a lot of data to go through I'm sure this will be a real help. |
#6
|
|||
|
|||
Excelledsoftware – thanks, I tried your method and it works pretty nifty, especially when you write the code for me!It provides the exact same results as mine, and just like yours, mine also allows you to select any cell you want after highlighting the matched cells.I also work with worksheets of hundreds of thousands of rows, and with a large spreadsheet, you have the potential of having many, many highlighted cells, regardless of the methodology.After all, that’s why you did the routine.
I admire your VBA skills, but there are many things that can be accomplished by us commoners without having to employ such skills.I don’t expect to have you on call to write code for me, so I have created other techniques using the built-in Excel functions. |
#7
|
|||
|
|||
You are very welcome. Just know that the more complicated your spreadsheet is the more you will want to know to use it. When I first started with Excel I would only do formulas and avoid VBA as much as I could using every excuse I could. As the spreadsheets grew I decided it might be time to give it a try and I am so glad I did. It always helps that if you are willing to learn it to have someone to reach out to. While I dont want you to think of me as a on the fly code writer I do want you to know that you can ask any questions and I would be more than happy to help you learn more.
|
#8
|
|||
|
|||
Thanks - I'll consider stepping up to the plate.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Find/Replace is not working - Excel 2010 | fieldhaven | Excel | 2 | 02-19-2014 08:30 AM |
2013 search results take a long time - they fill in as results in reverse date order | themookman | Outlook | 0 | 10-11-2013 12:01 PM |
Find and Replace using Excel range | dmarie123 | Word VBA | 15 | 04-02-2013 07:54 AM |
Find Results in excel copy the rows to another sheet | khalidfazeli | Excel | 2 | 02-06-2013 09:38 AM |
Needs help to find excel formula- Please | aamer_1983 | Excel | 2 | 07-13-2009 01:46 AM |