Highlighting only certain text

[tapsmiled]

Hello all,

I have the following code:

Please Login or Register  to view this content.

which is conducting a wildcard search of roughly 15,000 rows of data. It is a wildcard search comprised of street names. So, if a user types "Wood" in ComboBox1, it may return Woodmont, Northwood, or any other combination. Is there a way, when the search results are populated, to highlight only the "wood" (in this case) part of the response?

Thank you in advance!!!!

[tigertiger]

eliminating the loop in your sub, firstly, it becomes look likes that:

Please Login or Register  to view this content.

an other issue is HIGHLIGHT - that I do not understand your explain much, waiting other help

[tapsmiled]

Does eliminating the loop speed things up? I had some great help with this so far, and I'm trying to learn what the changes accomplish. Thanks in adance!

[Cerbera]

I know that you can use the below code to change the font of certain characters in a cell (in this case characters 1-4).

It should be pretty easy to adapt the code to highlight the word in the outputted results.

Please Login or Register  to view this content.

You could use an INSTR function to find the start point of the "search string" within the "result strings" and then change the font for Start:= "the result of INSTR", Length:= "Len of the search term".

Hope that this helps.

[tigertiger]

Does eliminating the loop speed things up? I had some great help with this so far, and I'm trying to learn what the changes accomplish. Thanks in adance!

yes, of course. the speed of the sub is improved dramatically

to solve the highlight issue, please could you post your example file?

[tapsmiled]

I inserted the new code, but I havent noticed any changes in speed. It takes 2-3 seconds to process the request.

Sadly, I cannot post the workbook as it is not "For Public Display".

[tapsmiled]

@cerbera: thanks for your help, but I am a bit of a novice, and what you put is completely foreign to me.

[tigertiger]

I inserted the new code, but I havent noticed any changes in speed. It takes 2-3 seconds to process the request.

You can test the change of speed bay added the code (timer)

Please Login or Register  to view this content.

and put the same timer to the old sub

Please Login or Register  to view this content.

you will easily noted the significant change

Sadly, I cannot post the workbook as it is not "For Public Display".

if not, you can make a dummy data, or give more description elaborately

[tapsmiled]

@Tiger: thanks, I will try to do that once I am able to resolve the highlighting issue.

[Cerbera]

Hi Tapsmiled,

Without seeing your code it's difficult to give an accurate answer, however I have put some "street names" in column A of a sheet and a search term in cell D1.

I then wrote the following code to highlight any occurrences of the "search term" within the cells in column A in bold.

This may not be the best code, and it's all dependant on what is in your sheet and what you want to do, but it may provide some inspiration

Please Login or Register  to view this content.

[Cerbera]

BTW, this uses xlDown to find the last cell without any content, but it falls over if there is an empty cell in the list - it doesn't carry on down. To get around this you could change the line to:

Please Login or Register  to view this content.

This starts at the bottom and finds the first filled cell (from the bottom).

[tapsmiled]

Thank you very much. I will check it tomorrow and see if it does what I would like. I appreciate your help.

[Cerbera]

Did the highlight help?

Obviously it doesn't collect the results and output them - just tackles the highlighting.

If you wanted to output the search results, you could always copy the list of street names to an array and then process through searching for the string. When you find one you could add the value to an output array with the character that the search string starts at in a second column in the output array.

Once you finished the search you can just output the values of the first column of the output array and use the second column value to implement the highlighting of the matched component.