conditional format fill based on another cell contains...

[rjmdc]

conditional format column E fill based on column I contains...
column I has many services i am looking for specific words as part of the contents and if it contains those words fill cell in column E
what would be the formula?

 

[Peter_SSs]

i am looking for specific words

How many words are you looking for? Less than 5? Could be a thousand?
Is the list of words stored in the same worksheet? In another worksheet? Not stored anywhere?

and if it contains those words

Does it have to contain all the words or would one be sufficient to colour the cell?

Is there any punctuation in the column I data?

What about a small set of dummy data and expected results with XL2BB so that we can be sure we understand the requirement?

 

[rjmdc]

column I has lists etc.
IDGS camp
IDGS school
OTPS transportation
OTPS Health and safety

i tried =(search("OTPS",$I1))
it gives me a fill
i want to know if its accurate

 

[Peter_SSs]

i tried =(search("OTPS",$I1))

You said in post 1 that you were looking for "specific words". You also said "if it contains those words" This formula is only looking for one word. I am confused. Can you clarify?

What about a small set of dummy data and expected results with XL2BB so that we can be sure we understand the requirement?

 

[rjmdc]

specific word
if contains IDGS hilite blue
if contains OTPPS hilite blue etc