Conditional Formatting

[Pauljj]

I need to format a cell in this case column I if a cell in this column contains the word "new" in any part of the text in that cell it should turn one colour otherwise if anything else is entered into this cell...other then "new" it should turn another colour.

Does anyone have any ideas ?

Cheers

Paul

 

[Norie]

Paul

Select column I, goto conditional formatting, select Formula Is from the dropdown and enter a formula like this.

=ISNUMBER(SEARCH("new",I1))

And format as required.

Note this isn't case sensitive, if you need it to be case sensitive use FIND instead of search.

 

[barry houdini]

you could use "formula is"

=SEARCH("new",A1)

although that will also then format cells containing "knew" or similar. Assuming you only have words and spaces (without punctuation) this is probably better

=SEARCH(" new "," "&A1&" ")

note that SEARCH is not case-sensitive, if you want to look for a specific case use FIND

 

[Pauljj]

Thank you for all you help