Highlight rows that contain

Generic formula 

=SEARCH(text,cocatenated_columns)

Summary 

If you want to highlight rows in a table that contain specific text, you use conditional formatting with a formula that returns TRUE when the the text is found. The trick is to concatenate (glue together) the columns you want to search and to lock the column references so that only the rows can change.

For example, assume you have a simple table of data in B4:E11 and you want to highlight all rows that contain the text "dog". Just select all data in the table and create a new conditional formatting rule that uses this formula:

=SEARCH("dog",$B4&$C4&$D4&$E4)