Conditionally Format Non-Blank Cell in Range When Adjacent Cell on Same Row Contains Either or Both of Two Specific Values

Question

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.

Answer 1

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.