Forum Discussion

Copper Contributor

Aug 24, 2020

Solved

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 crit...

wumolad
Aug 24, 2020
BobbyM23

If you prefer to "hardcode" the words in the formula, then this should do:

=OR(ISNUMBER(SEARCH("NEUROLOGY",$F2)),ISNUMBER(SEARCH("INTERVENTIONAL",$F2)),ISNUMBER(SEARCH("OPHTHALMOLOGY",$F2)))

Cheers

wumolad

Iron Contributor

Aug 24, 2020

BobbyM23

To make your conditional formatting more dynamic. You can type the words to search for in another column or sheet as shown in the attached.

Then use this formula:

=OR(ISNUMBER(SEARCH($I$2,$F2)),ISNUMBER(SEARCH($I$3,$F2)),ISNUMBER(SEARCH($I$4,$F2)))

Hope this is useful.

Cheers

Conditional Formatting_Search Multiple Words.xlsx9 KB

BobbyM23
Copper Contributor
Aug 24, 2020
wumolad I did see this option and I am being OCD and seeing if there is a way without setting the words in another column or table like that. I will keep this formula in my notes though in case I am not able to find what I am looking for.
- wumolad
  Iron Contributor
  Aug 24, 2020
  BobbyM23
  
  If you prefer to "hardcode" the words in the formula, then this should do:
  
  =OR(ISNUMBER(SEARCH("NEUROLOGY",$F2)),ISNUMBER(SEARCH("INTERVENTIONAL",$F2)),ISNUMBER(SEARCH("OPHTHALMOLOGY",$F2)))
  
  Cheers
  - BobbyM23
    Copper Contributor
    Aug 24, 2020
    wumolad That last code you provided is EXACTLY what I am looking for! After accidentally posting this exact question in a Google Sheets thread and it going on FOREVER and me trying different scenarios on my own, I FINALLY got what I want! Thank you SO MUCH!!!!