CODEWITHSUNDEEP
excel
Wally Hartshorn
Wally Hartshorn

Reputation: 915

Conditional Formatting Entire Row If Any Cell Contains the Specified Text

I'm trying to use conditional formatting to highlight all rows that contains a specific value in any of the cells of that row.

For example, if this is the spreadsheet:

     A            B            C             D           E
1    Town         Baseball     Basketball    Football    Soccer
2    Barksdale    Cardinals    Lions         Tigers      Lightning
3    Clinton      Bluejays     Bears         Cardinals   Tornadoes
4    Denton       Giants       Tigers        Blues       Hornets

I would like to highlight every row that contains 'Blue' anywhere in the row. In this case, it should highlight the "Clinton" row, because it has "Bluejays", and the "Denton" row, because it has "Blues".

I'm trying this as the formula:

=SEARCH("Blue",A1)

And I'm applying it to:

=$1:$1048576

Unfortunately, that highlights the specific cell containing "Blue", but not the entire row.

I've seen posts about formatting the entire row based upon the value of one cell, and I've seen posts about formatting one cell based upon the value of other cells in the row, but I can't seem to figure out how to combine those to work in my situation.

Any ideas?

Upvotes: 8

Views: 54984

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Try this formula for conditional formatting:

=COUNTIF(1:1,"*blue*")

enter image description here

Upvotes: 11

Related Questions