asked on

conditional formatting based on another cell containing specific text

Hi,

I have a situation where I want to format a cell based on whether a different cell contains a certain word. Is that possible to do?

Example:

Cell A3 is the cell to conditionally format
Cell D3 is the source cell for the conditional format of A3
D3 will contain a free form sentence
I want A3 to change colour if D3 contains the text "waiting" somewhere in that sentence

I already have a conditional formatting rule applying to D3 that changes its colour if it contains the specific text "waiting" which works great, I just don't see how to apply that rule to another cell in the way I described.

dlmille

Use this conditional format in A3:

=IFERROR(FIND("waiting",D3),0)>0

See attached - fill color set to yellow on TRUE. I searched for exactly that string, you might be interested in testing for different case sensitivity, let me know.

See attached.

Dave
condFormattingFun.xlsx

dlmille

Whoops - let's try that attachment again :)
condFormattingFun.xlsx

dlmille

Replace the FIND with SEARCH if you don't care about case sensitivity.

See attached:

Dave
condFormattingFun.xlsx

stretchr

ASKER

Ok yes that works, ideally though it would be case insensitive, just like the "specific text" formatting rule is.

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.