Google-sheets – Changing row colour if value set

conditional formattinggoogle sheets

I'm trying to use the following script to change the row colour if a value in column N is set:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var rows = sheet.getRange('n3:n700');
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 0; i <= numRows - 1; i++) {
    for (var x in values[i]){
      if (values[i][x] == '4'){
        var n = i + 1;
        sheet.getRange('a'+n+':n'+n).setBackgroundColor('red');
      }
    }
  }
};

But unfortunately I get the following error message:

Cannot call method "getRange" of null.

I've also tried switching to the new spreadsheets and using conditional formatting with a formula if(n3 = "") with no joy, and to be honest even if that worked I'm not sure if there is an easy way to apply conditional formatting to each line.

Best Answer

In new Google Sheets if you select A3:Z20 and Format, Conditional formatting... and enter: 

=$N3<>""

for Custom formula is with a colour of your choice and Range: A3:Z20 then in the range A3:Z20 every cell should be coloured if the cell in Column N for the corresponding row is not empty.

Since such formatting is conditional the IF is not normally required.

Related Topic