0

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.

conditional format range

0
0

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
1
  • This works beautifully; thank you Rubén. May 24 at 3:07

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.