Forum Discussion
Conditional formatting for entire row if one of multiple words appears in a column
I need help setting a conditional format for my spreadsheet. My data goes from cell A1 thru I whatever depending on the size of my report each day. Row 1 is my header line.
Column F is where my criteria I want to zero in on is. It contains text such as Neurology Dept, Interventional Pain Dept, Ophthalmology Dept and so on. I have a good set of formulas to do what I want so far, but am having issues with one part.
I want to highlight the entire row when "NEUROLOGY" is found in Column F and to do that, I am using the following formula.
=SEARCH("NEUROLOGY", $F2)
I want to be able to use one formula to search for multiple words in Column F and highlight the row if it finds any of them, instead of using the above formula three different times for each word. I would like it to search for NEUROLOGY, INTERVENTIONAL and OPHTHALMOLOGY and apply the same formatting if it finds any of those words in Column F. I can't get this to work for the life of me!
8 Replies
How do you do a conditional formatting if I have a word in column Y that I have to match to multiple words in column B, and I have to have the entire row highlighted A-AH. example is Ready in Y, and only the words, not available, and away in B needs to have the row highlighted.
Highlight all the cells down F1 you want to format. Click on Conditional formatting and select New Rule, Use Formula. put =OR(F1="Neurology",F1="Intervention",F1="Ophthalmology") then select Format, and choose your format. Say ok until you are out.
SqueakySneakers That won't work because the cells in column F contain more than just the word Neurology. For instance my column F will read, Neurology Danville D1 or Neurology Danville D2 and I want my formula to highlight the entire row any time it finds Neurology anywhere in the cell in column F.
