[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

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.
0
stretchr
Asked:
stretchr
  • 4
  • 2
2 Solutions
 
dlmilleCommented:
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
0
 
dlmilleCommented:
Whoops - let's try that attachment again :)
condFormattingFun.xlsx
0
 
dlmilleCommented:
Replace the FIND with SEARCH if you don't care about case sensitivity.

See attached:

Dave
condFormattingFun.xlsx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
stretchrAuthor Commented:
Ok yes that works, ideally though it would be case insensitive, just like the "specific text" formatting rule is.
0
 
dlmilleCommented:
I gave you both options in my last post

FIND - case sensitive

SEARCH (same formula, just change the function) - no case sensitivity

Dave
0
 
stretchrAuthor Commented:
sorry I only saw your first post before I posted! Am home now so will try this at work on Monday - but sounds like it will do the trick.
0
 
barry houdiniCommented:
I see Dave has given you a good answer. Note that errors equate to FALSE in conditional formatting (and any number except zero is equivalent to TRUE) so this condition would suffice

=SEARCH("waiting",D3)

regards, barry
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now