Highlighting all cells in sheet that contain String

[CriticalMass048]

What if you just want to search for a string, like how you would use Ctrl-F in Word? I want to be able to search a list of names and type something like "Johns" and have Excel highlight all cells containing "Johnson", "Johnstone", etc to make finding someone's name easier.

Thanks in advance!

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Logit]

Please Login or Register  to view this content.

[CriticalMass048]

Logit, thank you for the advice and programming, but that wasn't exactly what I wanted.

I'm looking for only a partial string in the list, so if I'm searching for someone's name quickly in a list that is not alphabetical, I can find it. For instance, if I'm looking for "Boris Johnson" in a list of names, I could type only "Boris" or "Johns" and any cell containing those values would "light up" so that a user could find it quicker.

I just thought there would be a "Hotkey" command, like Ctrl-F. For some reason, when I use Ctrl-F in Excel, even if I type "Boris Johnson", I get told Excel couldn't find what I was looking for. Maybe I'm doing something wrong.

[Pete_UK]

You could set up conditional formatting to highlight a range of cells (individually) if there is a partial match with that string. Suppose you use M1 to enter "Boris" (without the quotes), then your CF formula would be:

=ISNUMBER(FIND($X$1,A2))

assuming A2 is the starting cell where you want to search for the name. You can use the Applies To option to define the range.

Hope this helps.

Pete

[Logit]

Please Login or Register  to view this content.

[CriticalMass048]

Pete_UK: Thanks! Other than the fact that it's case-sensitive, and when the cell is blank, the entire column is highlighted, it works great! I can try to go in and change the CF to only format if the cell has a value in it, but I can live with it either way!

Logit: Unfortunately, I just saw your second attempt. I will try that one as well! Thanks!

[mehmetcik]

Building on Pete's work in post 5.

Try: =AND(A1<>"",SEARCH($X$1,A1))

[Rick Rothstein]

Here is a non-looping macro that should be quite fast. It starts by asking you the text you want to search for and then "uncolors" any existing colored cells and then colors the cells that match your search criteria.

Please Login or Register  to view this content.