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

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