Creating search box using macro and highlighting search results in the same sheet

[a1mAtm3]

Hi all,
I am new to this forum and I have minimal knowledge on macro in excel. I have a problem and I hope you guys can help.
I have a single worksheet containing text data and all the while I used Ctrl+F to find the texts that I need.

I need to have a macro to create a search box and can highlight (any colour will do) all the cells that match the input in the search box.

Eg. the cell data has "abcd efgh ijkl" and when I search for "fgh", the cell has to be highlighted. Cells that did not match will be left not-highlighted.
So, if let's say there are 3 cells that match the input which is "fgh", these 3 cells will be highlighted.

I need to have an active macro so that I will not need to activate the macro every time I want to search the list.
That means the search bar has to be permanently on the sheet, preferably on top of the sheet.
So that I can directly locate the cells after I key in the data on the search box and press "Search".
If there is no result, then all cells should be left not-highlighted.

Thanks in advance.

 

[VBA Geek]

Hello,

conditional formatting could do the trick for you. Do you want to match the entire cell content of just part of it

for example

if you search for the word "Hair"

just cells with the word "Hair" need to be highlighted or also "Hairdresser", "Hairstraightener" etc

 

[a1mAtm3]

Hi,
Thanks for the reply.
I want to match just part of it.
From the example you given, the cells with word "Hair", "Hairdresser","Hairstraightener" etc need to be highlighted.

Sorry that I forgot to mention this earlier; I need the sheet to clear the highlighted colour when I do second search and onwards.
Example: Data in cells; "aa1234", "ab1234", "ac1234", "aa9999", "ab9999"
1st search (if i key in "aa"), then the cells with "aa1234" and "aa9999" will be highlighted.
Then, when i do 2nd search (if i key in "ab" this time), "ab1234" and "ab9999" will be highlighted, and at the same time "aa1234" and "aa9999" will be "de-highlighted" (back to normal without colour) since they do not match my search.
And when i do 3rd search (if i key in "ac" this time), only "ac1234" will be highlighted while the rest of the cells "de-highlighted".

 

[VBA Geek]

ok

suppose the list

[TABLE="width: 193"]
<TBODY>[TR]
[TD]aa1234</SPAN>[/TD]
[TD]ab1234</SPAN>[/TD]
[/TR]
[TR]
[TD]ac1234</SPAN>[/TD]
[TD]aa9999</SPAN>[/TD]
[/TR]
[TR]
[TD]ab9999</SPAN>[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]


is in A1:B3,

your keyword to be searched in F1

highlight the range A1:B3, then as it is highlighted, press on your keyboard ALT+O+D, new RULE, Use a formula to determine which cell to format

and enter the formula

=ISNUMBER(SEARCH($F$1,A1))

then choose the format you like and press ok

 

[a1mAtm3]

Oh wow! It works flawlessly.
Thank you so much!!!