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;

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.


1 Answer 1


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


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.

  • :) Thank you. It was very useful to know how to apply it to my entire document too!
    – Nick
    Commented Apr 15, 2014 at 8:41
  • @pnuts Do you how to get this working when the matched criteria is in a dropdown field (of a validation dialog)? I guess <> stands for 'contains', I'd need an exact match, like when I have AAA, BBB, CCC in the dropdown and I only want to match BBB.
    – patrick
    Commented Mar 14, 2018 at 9:02
  • Okay, found it (example with dialog value in column D): =$D:$D="BBB".
    – patrick
    Commented Mar 14, 2018 at 9:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.