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

conditional formattinggoogle sheets

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.

Best Answer

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

Related Solutions

Google-sheets – How to check if a cell is blank for Conditional formatting

With the range set to: B3:O1000

Try:

=AND(ISBLANK(B3),INT($A3)=INT(today()))

or even shorter:

=AND(ISBLANK(B3),$A3=today())

How to Update Row in Google Sheets Based on Cell Value

ss.getActiveRange() selects the currently selected or edited range. From reading your script I see you meant the entire range of data, which would be ss.getDataRange(). Other than that, the logic is okay.

Other suggestions:

Don't use methods when unnecessary, you get time and tablet that are never used.
Consider getting all relevant values in one getValues call, and then loop over the resulting JavaScript array, referring to individual cells with range.getCell(..).clear only when actually needed.
Consider running the script by a trigger on form submission, making use of the event object. This simplifies things a lot: see below.

function statCheck(e) {
  if (e.values[4] === 'Recovering) {  
    e.range.offset(0, 1, 1, 2).clear();
    e.range.offset(0, 4, 1, 1).setValue('RECOVERED!');
  }
}

The index is 4 instead of 5 because JavaScript index is 0-based.

Best Answer

Related Solutions

Google-sheets – How to check if a cell is blank for Conditional formatting

How to Update Row in Google Sheets Based on Cell Value

Related Topic