Search Cells and Highlight Found Entries
Imagine you have a long list in your excel sheet and want to find certain entries. Especially if the list gets bigger, it'd be nice to just tell Excel what you're looking for, and have Excel automatically find it. It's faster and more accurately than searching yourself.
Because it's always easier to explain things using examples - here's one, a list of songs from a CD collection, but of course this could be any list with any content and certainly a much longer list than this one...
Of course you could simply use the built-in Excel search via Edit - Find.
This yields a result but:
Now you could write a VBA macro that formats all entries that match the search...
But besides highlighting the matching entries, you'll also need code to remove the highlighting from entries that don't match your search. If you have a plain list with no or just simple formatting, then this is relatively easy, but the more different formats are in your list (like the example, with every other row colored differently), the more code you'll need to restore them.
And if someone else wants to use the list, but doesn't like the colors and changes them, they'll be in for an unpleasant surprise when their new layout gets replaced by the old formatting after they did a search.
Well, with some more effort you could read out and save the original formatting of each cell before changing it, and later restore it to the saved values, but this is a lot of work that you don't need to do because Excel can do it much easier and faster.
Last but not least, VBA code runs always slower than built-in Excel functionality. If your list is huge, this will make a big difference in performance.
The key word is Conditional Formatting. You can define a conditional format for the list that shows only when the cell value matches the search value. This method has the advantage that you don't have to worry about the original cell formats at all.
Finding exact matches
You'll need a cell where the search value goes in. In our example, this will be cell B1.
And then you set up the conditional formatting as follows:
Looking for parts of strings
The above example is neat, but it will only find entries that match exactly the search string. If, for instance, you'd like to search for songs with the Bee Gees, the result will be incomplete; the duet with Celine Dion won't be highlighted because it doesn't match the search exactly.
Unfortunately, there's no option Cell Value Is LIKE for conditional formatting...
But this is still no reason to resort to VBA. Conditional Formatting can also evaluate formulas, and the worksheet function FIND can search for substrings, so we'll make use of this:
Searching with wildcards
You'll notice that the worksheet function FIND is case sensitive, so searching for bee gees won't find anything. It also can't handle wildcards.
If you want to use such features, you should use the worksheet function SEARCH instead of FIND.
You can also use wildcards now. A ? marks any single character and * marks any sequence of characters. If you actually want to search for a question mark or an asterisk, put a ~ before the character.
Highlight the entire row
But you don't just want to highlight the found entry, but the entire row of the list?
This is also possible, you'll just have to adapt the check formula to check all cells of the current row instead of the current cell alone, and combine the results.
If you enter a search string now, entire rows of the list get highlighted, not only the cell with the matching value:
Ok, so this post didn't contain VBA code, but only because it was easier to do this without...
Because it's always easier to explain things using examples - here's one, a list of songs from a CD collection, but of course this could be any list with any content and certainly a much longer list than this one...
Of course you could simply use the built-in Excel search via Edit - Find.
This yields a result but:
- You need to select the entire list before each search. If you have a huge list, this can be annoying. Or you don't select anything, then Excel will search the entire sheet and perhaps will find matches in cells that don't belong to the list.
- Excel selects only one found cell at a time, not all cells that match the search at the same time.
- If you choose Find All, Excel still won't select all found cells, but gives you a list of them, and you have to look at the sheet yourself to find the given addresses. And the list will be gone when you close the Find and Replace dialog box.
Now you could write a VBA macro that formats all entries that match the search...
But besides highlighting the matching entries, you'll also need code to remove the highlighting from entries that don't match your search. If you have a plain list with no or just simple formatting, then this is relatively easy, but the more different formats are in your list (like the example, with every other row colored differently), the more code you'll need to restore them.
And if someone else wants to use the list, but doesn't like the colors and changes them, they'll be in for an unpleasant surprise when their new layout gets replaced by the old formatting after they did a search.
Well, with some more effort you could read out and save the original formatting of each cell before changing it, and later restore it to the saved values, but this is a lot of work that you don't need to do because Excel can do it much easier and faster.
Last but not least, VBA code runs always slower than built-in Excel functionality. If your list is huge, this will make a big difference in performance.
The key word is Conditional Formatting. You can define a conditional format for the list that shows only when the cell value matches the search value. This method has the advantage that you don't have to worry about the original cell formats at all.
Finding exact matches
You'll need a cell where the search value goes in. In our example, this will be cell B1.
And then you set up the conditional formatting as follows:
- Select all cells of your list, then go to Format - Conditional Formatting...
- Select from the drop down menus Cell Value Is and equal to. In the last input field, select the search cell or enter manually the address: =$B$1
- Click on Format... and select how you want the matching entries being formatted, in our example it's a bold font and a red cell background.
- Select OK twice.
Looking for parts of strings
The above example is neat, but it will only find entries that match exactly the search string. If, for instance, you'd like to search for songs with the Bee Gees, the result will be incomplete; the duet with Celine Dion won't be highlighted because it doesn't match the search exactly.
Unfortunately, there's no option Cell Value Is LIKE for conditional formatting...
But this is still no reason to resort to VBA. Conditional Formatting can also evaluate formulas, and the worksheet function FIND can search for substrings, so we'll make use of this:
- Select the first cell of your list (here A4) and go to Format - Conditional Formatting...
- Select Formula Is from the drop down, and then enter the following formula:
=AND(FIND($B$1;A4)>0;$B$1<>"")
FIND returns the position of the found match, so FIND($B$1;A4)>0 will always be TRUE if a match is found ($B$1 being the address of the cell with the search string, and A4 being the address of the current cell, which the formula is entered for). Since FIND always returns 1 if the search string is empty, we have also to check that it's not empty with $B$1<>"", or all entries would be highlighted if no search string was entered. And the function AND finally combines these two checks, so that both conditions must be True to highlight a cell. - Now copy the format to all other cells of the list.
Note that the address A4 was written in the formula as a relative reference, contrary to $B$1, which is an absolute reference. So the reference to A4 will be adapted accordingly when copying the formula to other cells, having it always refer to the cell where the formula is.
Select A4, then go to Edit - Copy
Select the entire list, then go to Edit - Paste Special...
Select Formats and press OK.
Unfortunately you can't copy the conditional formatting only (not without use of VBA code), but all formats. So you'll probably have to repair the layout formatting of your list afterwards.
Searching with wildcards
You'll notice that the worksheet function FIND is case sensitive, so searching for bee gees won't find anything. It also can't handle wildcards.
If you want to use such features, you should use the worksheet function SEARCH instead of FIND.
- Select the first cell of your list (A4 in our example) and change the formula for the conditional formatting:
=AND(SEARCH($B$1;A4)>0;$B$1<>"")
- Then copy the format to the other cells (Copy and Paste Special...) and repair the layout of the list if necessary.
You can also use wildcards now. A ? marks any single character and * marks any sequence of characters. If you actually want to search for a question mark or an asterisk, put a ~ before the character.
Highlight the entire row
But you don't just want to highlight the found entry, but the entire row of the list?
This is also possible, you'll just have to adapt the check formula to check all cells of the current row instead of the current cell alone, and combine the results.
- Select the first cell of your list (A4 in our example) and adapt the formula for the conditional formatting as follows:
=AND(NOT(AND(ISERROR(SEARCH($B$1;$A4));ISERROR(SEARCH($B$1;$B4));ISERROR(SEARCH($B$1;$C4))));$B$1<>"")
SEARCH (also FIND) returns an error when the search string is not found. So in the formula from before, SEARCH($B$1;A4)>0 returned TRUE in case of success, and an error when the string wasn't found. Since the cell was highlighted in case the result was TRUE, and in all other cases not, it didn't hurt that an error was returned and not FALSE.
But if you combine several logical values with AND (or OR), it's important to have TRUE / FALSE and no error values. So we use the function ISERROR to evaluate the result of the SEARCH function. Because ISERROR returns TRUE in case of error, i.e. string not found, and FALSE for no error, i.e. string was found, we have to negate the result with NOT.
Please also note that the column of the cells to be checked is now an absolute reference (with a $ before), because the column is fix and must not be changed when copying the format to the other cells. - Again, copy the format with Copy and Paste Special... to the other cells of the list, and repair the layout if necessary.
If you enter a search string now, entire rows of the list get highlighted, not only the cell with the matching value:
Ok, so this post didn't contain VBA code, but only because it was easier to do this without...