How to highlight the whole row when any one column contains a certain word?

Question

I have an excel sheet with multiple columns. The column names are FirstName, LastName, EmailAddress, PostalAddress, etc. Sometimes, either column will have the word 'Account' in it. Eg; FirstName = Accounts, LastName = Payable, EmailAddress = [email protected]

I want to highlight the entire row where any of the columns have the word 'account' in it, so that I can remove those rows.

I've tried selecting the entire sheet and then under Conditional Formatting > Highlight Cell Rules > Text That Contains > I've included the word Account. This only highlights the cell that has the word account in any column. I would like to highlight the entire row.

How do I go about highlighting the entire row? Also, is there a way to highlight all rows with the word 'Account' and in all worksheets of the same Excel file?

Answer 1

Suppose your data starting in row 2. So, highlight all the rows and go to: Conditional Formatting > New Rule > Use a formula...

Then type in the following:

=CountIf(2:2, "*account*")

And make sure the correct region is shown in the Applies To.

And that should do it.