Conditionally Format Non-Blank Cell in Range When Adjacent Cell on Same Row Contains Particular Value

Question

I am trying to conditionally format the non-blank cell within a range when an adjacent cell on the same row contains a particular alpha value (ach in this instance, regardless whether ACH is written in upper case, lower case or a mix of upper/lower case).

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

=COUNTIF($D2,"ach")

=$D2="ach"

Both formulas highlight the blank cells in the row as well as the cell populated with a value.

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

Answer 1

Instead of the formulas that you tried use:

=AND(NOT(ISBLANK(A2)),LOWER($D2)="ach")

• LOWER will change the value to lower case
• ISBLANK returns TRUE if the current cell is blank
• NOT converts a TRUE value to FALSE and viceversa
• AND returNs TRUE when all the parameters are TRUE otherwise returns FALSE