This blog post shows you how to highlight rows where text string criteria are found.

Example, continents criterion (cell B3) is only searched in column Continents (B7:B28). Color criterion is searched for in column Color, and so on.

Conditional formatting formula:

=COUNT(SEARCH($B$3, $B7)+1/($B7<>""))*COUNT(SEARCH($C$3, $C7)+1/($C7<>""))*COUNT(SEARCH($D$3, $D7)+1/($D7<>""))*COUNT(SEARCH($E$3, $E7)+1/($E7<>""))

The formula is not case sensitive. Replace Search() functions with Find() to make it case sensitive.

How to apply conditional formatting formula

Make sure you adjust cell references to your excel sheet.

  1. Select cells B6:E27
  2. Click "Home" tab
  3. Click "Conditional Formatting" button
  4. Click "New Rule.."
  5. Click "Use a formula to determine which cells to format"
  6. Type =COUNT(SEARCH($B$3, $B7)+1/($B7<>""))*COUNT(SEARCH($C$3, $C7)+1/($C7<>""))*COUNT(SEARCH($D$3, $D7)+1/($D7<>""))*COUNT(SEARCH($E$3, $E7)+1/($E7<>"")) in "Format values where this formula is TRUE" window.
  7. Click "Format.." button
  8. Click "Fill" tab
  9. Select a color for highlighted cells.
  10. Click "Ok"
  11. Click "Ok"
  12. Click "Ok"

How the conditional formatting formula works in cell B12

=COUNT(SEARCH($B$3, $B7)+1/($B7<>""))*COUNT(SEARCH($C$3, $C7)+1/($C7<>""))*COUNT(SEARCH($D$3, $D7)+1/($D7<>""))*COUNT(SEARCH($E$3, $E7)+1/($E7<>""))

Step 1 - Understand relative and absolute cell references

Formula In cell B7:

=COUNT(SEARCH($B$3, $B7)+1/($B7<>""))*COUNT(SEARCH($C$3, $C7)+1/($C7<>""))*COUNT(SEARCH($D$3, $D7)+1/($D7<>""))*COUNT(SEARCH($E$3, $E7)+1/($E7<>""))

Formula changes in cell B12 to:

=COUNT(SEARCH($B$3, $B12)+1/($B12<>""))*COUNT(SEARCH($C$3, $C12)+1/($C12<>""))*COUNT(SEARCH($D$3, $D12)+1/($D12<>""))*COUNT(SEARCH($E$3, $E12)+1/($E12<>""))

Step 2 - Search cells to see if criteria are found

=COUNT(SEARCH($B$3, $B7)+1/($B7<>""))*COUNT(SEARCH($C$3, $C7)+1/($C7<>""))*COUNT(SEARCH($D$3, $D7)+1/($D7<>""))*COUNT(SEARCH($E$3, $E7)+1/($E7<>""))

becomes

=COUNT(SEARCH("am", $B12)+1/($B12<>""))*COUNT(SEARCH("b", $C12)+1/($C12<>""))*COUNT(SEARCH("4", $D12)+1/($D12<>""))*COUNT(SEARCH("LA", $E12)+1/($E12<>""))

becomes

=COUNT(SEARCH("am", "America")+1/("America"<>""))*COUNT(SEARCH("b", "blue")+1/("blue"<>""))*COUNT(SEARCH("4", "Product 4")+1/("Product 4"<>""))*COUNT(SEARCH("LA", "Large")+1/("Large"<>""))

becomes

=COUNT(1+1/("America"<>""))*COUNT(1+1/("blue"<>""))*COUNT(9+1/("Product 4"<>""))*COUNT(1+1/("Large"<>""))

Step 3 - Identify empty criteria and return TRUE or FALSE

=COUNT(1+1/("America"<>""))*COUNT(1+1/("blue"<>""))*COUNT(9+1/("Product 4"<>""))*COUNT(1+1/("Large"<>""))

becomes

=COUNT(1+1/(TRUE))*COUNT(1+1/(TRUE))*COUNT(9+1/(TRUE))*COUNT(1+1/(TRUE))

becomes

=COUNT(1+1)*COUNT(1+1)*COUNT(9+1)*COUNT(1+1)

becomes

=COUNT(2)*COUNT(2)*COUNT(10)*COUNT(2)

and returns 1 (TRUE). Row 12 is highlighted

Download excel example file
quickly-highlight-records-in-a-list-search-function-AND-logic.xlsx
(Excel 2007 Workbook *.xlsx)

Functions:

COUNT(value_1, value_2) Counts the number of cells in a range that contain numbers

SEARCH(find_text, within_text, [start_num]) Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)