I am using an If statement depending on a relative column - cannot get it to work

Question

I am using google sheets. I have checkboxes in cells E1:AD1. When one of these checkboxes are checked, I want the cell in row 1 of that column to have the background coloured to green.

Eventually I want to be able to do the same to do different rows in the column. Please see my onEdit script below.

/** @OnlyCurrentDoc */

function onEdit(e) {

//This IF statement ensures that this onEdit macro only runs when cells E1:AD1 are edited in the sheet named "Finances 2020"
if (
e.source.getSheetName() == "Finances 2020" &&
e.range.columnStart == 5 &&
e.range.columnEnd == 30 &&
e.range.rowStart >= 1 &&
e.range.rowEnd <= 1 
) { 

//This if statement checks if the checkbox was checked or unchecked:
var checkboxtest = e.range.getValue()
if (checkboxtest == true) {

  //If so, colour the cell in row 1 of that column as green:
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var Finances_2020 = spreadsheet.getSheetByName("Finances 2020");
  var Fortnightcolumn = e.range.columnStart      
  Finances_2020.getRange(1, Fortnightcolumn).setBackground('#d9ead3');

}else{

}
}
}
;

Answer 1

• If you want to apply the background change for any edited cell in row 1, between the columns 5 and 30 - the correct statement would be:

if (e.source.getSheetName() == "Finances 2020" && 5<=e.range.getColumn()<=30 && e.range.getRow() == 1)

• Also, keep in mind that you enter the first if loop only if the sheet name is Finances 2020. Thus, to set the background color, you do not need to address the sheet separately, but can instead define e.range.setBackground('#d9ead3');.

SUMMARY

function onEdit(e) {
//This IF statement ensures that this onEdit macro only runs when cells E1:AD1 are edited in the sheet named "Finances 2020"
if (e.source.getSheetName() == "Finances 2020" &&
5<=e.range.getColumn()<=30 &&
e.range.getRow() == 1) { 
  //This if statement checks if the checkbox was checked or unchecked:
  var checkboxtest = e.range.getValue();
  if (checkboxtest == true) {
    //If so, colour the cell in row 1 of that column as green:
    e.range.setBackground('#d9ead3');
    }
  }
}

If you want to apply the background change also to other rows, e.g. rows 1 to 5 - change the condition of the if statement from ...&& e.range.getRow() == 1 to ...&& 1 <= e.range.getRow() <= 5