0

Building on a question I asked here, I am trying to conditionally format the non-blank cell within a range when an adjacent cell on the same row contains either or both of two specific values ("ach" and/or "wire").

So far I've tried to apply the following custom formulas to the range A2:C11:

=OR(ISNUMBER(SEARCH("ach",$D2)),ISNUMBER(SEARCH("wire",$D2)))

The above formula highlights the entire row from columns A:C if the cell on the same row in column D contains either ach, wire or both of these values. This is close, but I need only the non-blank cell on the row to be highlighted.

=OR(NOT(ISBLANK(A2)),ISNUMBER(SEARCH("ach",$D2)),ISNUMBER(SEARCH("wire",$D2)))

This formula does the same as the first; additionally it highlights the non-blank cells on the rows where column D does not contain either of the searched-for values (ach or wire).

I've included a simplified example here; the result I'm actually hoping for is depicted in the image below.

enter image description here

0

You will need both an and and an or to implement that logic. You can do that with this conditional formatting custom formula rule:

=not(isblank(A2)) * ( isnumber(search("ach",$D2)) + isnumber(search("wire",$D2)) )

See the new Solution sheet in your sample spreadsheet.

Alternatively, use regexmatch() like this:

=not(isblank(A2)) * regexmatch($D2, "(?i)ach|wire")

These formulas use Boolean arithmetic but you can use and() instead of * and or() instead of + if that is your preference.

1
  • Both of your solutions work beautifully, @doubleunary. I particularly like how concise your regexmatch() solution is. Thank you! Jun 13 at 22:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.