SOLVED

Conditional formatting for entire row if one of multiple words appears in a column

%3CLINGO-SUB%20id%3D%22lingo-sub-1607928%22%20slang%3D%22en-US%22%3EConditional%20formatting%20for%20entire%20row%20if%20one%20of%20multiple%20words%20appears%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1607928%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20help%20setting%20a%20conditional%20format%20for%20my%20spreadsheet.%20My%20data%20goes%20from%20cell%20A1%20thru%20I%20whatever%20depending%20on%20the%20size%20of%20my%20report%20each%20day.%20Row%201%20is%20my%20header%20line.%3CBR%20%2F%3EColumn%20F%20is%20where%20my%20criteria%20I%20want%20to%20zero%20in%20on%20is.%20It%20contains%20text%20such%20as%20Neurology%20Dept%2C%20Interventional%20Pain%20Dept%2C%20Ophthalmology%20Dept%20and%20so%20on.%20I%20have%20a%20good%20set%20of%20formulas%20to%20do%20what%20I%20want%20so%20far%2C%20but%20am%20having%20issues%20with%20one%20part.%3C%2FP%3E%3CP%3EI%20want%20to%20highlight%20the%20entire%20row%20when%20%22NEUROLOGY%22%20is%20found%20in%20Column%20F%20and%20to%20do%20that%2C%20I%20am%20using%20the%20following%20formula.%3CBR%20%2F%3E%3DSEARCH(%22NEUROLOGY%22%2C%20%24F2)%3C%2FP%3E%3CP%3EI%20want%20to%20be%20able%20to%20use%20one%20formula%20to%20search%20for%20multiple%20words%20in%20Column%20F%20and%20highlight%20the%20row%20if%20it%20finds%20any%20of%20them%2C%20instead%20of%20using%20the%20above%20formula%20three%20different%20times%20for%20each%20word.%20I%20would%20like%20it%20to%20search%20for%20NEUROLOGY%2C%20INTERVENTIONAL%20and%20OPHTHALMOLOGY%20and%20apply%20the%20same%20formatting%20if%20it%20finds%20any%20of%20those%20words%20in%20Column%20F.%20I%20can't%20get%20this%20to%20work%20for%20the%20life%20of%20me!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608072%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20if%20one%20of%20multiple%20words%20appears%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769007%22%20target%3D%22_blank%22%3E%40BobbyM23%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHighlight%20all%20the%20cells%20down%20F1%20you%20want%20to%20format.%20Click%20on%20Conditional%20formatting%20and%20select%20New%20Rule%2C%20Use%20Formula.%20put%20%3DOR(F1%3D%22Neurology%22%2CF1%3D%22Intervention%22%2CF1%3D%22%3CSPAN%3EOphthalmology%3C%2FSPAN%3E%22)%20then%20select%20Format%2C%20and%20choose%20your%20format.%20Say%20ok%20until%20you%20are%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608087%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20if%20one%20of%20multiple%20words%20appears%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608087%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769007%22%20target%3D%22_blank%22%3E%40BobbyM23%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20make%20your%20conditional%20formatting%20more%20dynamic.%20You%20can%20type%20the%20words%20to%20search%20for%20in%20another%20column%20or%20sheet%20as%20shown%20in%20the%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20use%20this%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DOR(ISNUMBER(SEARCH(%24I%242%2C%24F2))%2CISNUMBER(SEARCH(%24I%243%2C%24F2))%2CISNUMBER(SEARCH(%24I%244%2C%24F2)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20is%20useful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608112%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20if%20one%20of%20multiple%20words%20appears%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608112%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F628423%22%20target%3D%22_blank%22%3E%40SqueakySneakers%3C%2FA%3E%26nbsp%3BThat%20won't%20work%20because%20the%20cells%20in%20column%20F%20contain%20more%20than%20just%20the%20word%20Neurology.%20For%20instance%20my%20column%20F%20will%20read%2C%20Neurology%20Danville%20D1%20or%20Neurology%20Danville%20D2%20and%20I%20want%20my%20formula%20to%20highlight%20the%20entire%20row%20any%20time%20it%20finds%20Neurology%20anywhere%20in%20the%20cell%20in%20column%20F.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608115%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20if%20one%20of%20multiple%20words%20appears%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608115%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%20I%20did%20see%20this%20option%20and%20I%20am%20being%20OCD%20and%20seeing%20if%20there%20is%20a%20way%20without%20setting%20the%20words%20in%20another%20column%20or%20table%20like%20that.%20I%20will%20keep%20this%20formula%20in%20my%20notes%20though%20in%20case%20I%20am%20not%20able%20to%20find%20what%20I%20am%20looking%20for.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608190%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20if%20one%20of%20multiple%20words%20appears%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608190%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769007%22%20target%3D%22_blank%22%3E%40BobbyM23%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20prefer%20to%20%22hardcode%22%20the%20words%20in%20the%20formula%2C%20then%20this%20should%20do%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DOR(ISNUMBER(SEARCH(%22NEUROLOGY%22%2C%24F2))%2CISNUMBER(SEARCH(%22INTERVENTIONAL%22%2C%24F2))%2CISNUMBER(SEARCH(%22OPHTHALMOLOGY%22%2C%24F2)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608533%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20if%20one%20of%20multiple%20words%20appears%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608533%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%26nbsp%3BThat%20last%20code%20you%20provided%20is%20EXACTLY%20what%20I%20am%20looking%20for!%20After%20accidentally%20posting%20this%20exact%20question%20in%20a%20Google%20Sheets%20thread%20and%20it%20going%20on%20FOREVER%20and%20me%20trying%20different%20scenarios%20on%20my%20own%2C%20I%20FINALLY%20got%20what%20I%20want!%20Thank%20you%20SO%20MUCH!!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1608706%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20for%20entire%20row%20if%20one%20of%20multiple%20words%20appears%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1608706%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769007%22%20target%3D%22_blank%22%3E%40BobbyM23%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGlad%20to%20hear%20the%20information%20provided%20was%20useful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20enjoy%20the%20rest%20of%20the%20week.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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!

7 Replies

@BobbyM23 

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.

 

@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 

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

@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.
Best Response confirmed by BobbyM23 (New Contributor)
Solution

@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 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!!!!

@BobbyM23 

 

Glad to hear the information provided was useful.

 

Do enjoy the rest of the week.

 

Cheers