Highlight cell in colA if cells B-J on row are empty

Question

I have a simple spreadsheet in a Google Drive spreadsheet that contains in colA a list of names. ColB thru ColJ are fill in the blank cells.

I want to change the background color of the cell in ColA when there is no data entered in ColB thru ColJ but I do not see a way to do this.

Mogsdad Mogsdad · Accepted Answer · 2013-05-29T03:01:47

You'd think that conditional formatting would provide this capability, but it does not allow you to reference other cells. (Unlike Excel.)

Here is a script that will do the job. (If you're new to Google Apps Script, read this introduction.)

/**
 * The onEdit() function, when defined, is automatically invoked whenever the
 * spreadsheet is edited. (Not every change is an "edit".)
 *
 * If all of the "fill in the blanks" columns are empty, set the
 * background color of the name column in that row.
 */
function onEdit(event) {
  var row = event.range.getRow();
  var sheet = event.range.getSheet();
  var cellA = sheet.getRange(row,1);
  // Get the range of cells B-J that make up the "fill in the blanks" range...
  var fillInTheBlanks = sheet.getRange(row, 2, 1, 9)
                             .getValues()[0]  // ... get values in 0-th row
                             .join('');       // ... and join into one string

  // Check that string - if it is empty, the range was blank,
  // and we should set the background color to, say, yellow.
  if (fillInTheBlanks.length == 0) {
    cellA.setBackground("yellow");
  }
  // On the other hand, if the string was NOT blank, there is something
  // in the "fill in the blanks" range, so we will clear the background.
  else {
    cellA.setBackground("white");
  }
}

/**
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 *
 * Iterates through all rows in spreadsheet, faking onEdit triggers, as if
 * each name has been edited.
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var lastRow = sheet.getLastRow();
  for (var row=1; row <= lastRow; row++) {
    onEdit({range:sheet.getRange('A'+row)});
  }
};

Highlight cell in colA if cells B-J on row are empty

2 Answers