I have 2 sheets: SheetA and SheetB with identical values in corresponding cells. I'm trying to change the cell color in SheetA when the value of the cell in SheetB changes. For example if cell A5 has value 10 in both sheets and I then change it to 5 in SheetB, I want the background color of the cell in SheetA to change

This is the code so far

function onEdit(e){
  
  var sheetsToWatch = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetA");
  var sheetsToEdit = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetB"); 
  var cell = sheetsToEdit.getActiveCell();  // checking the active cell
  var bg = '#faec15'

  for (let i = 0; i < active_sheet.length; i  ) {
    if (sheetsToWatch.match(sheetsToEdit[i])) {
      sheetsToWatch.getRange(cell.getRow(), cell.getColumn()).setBackground(bg);
    } else {
      return;
    }
  } 
}

CodePudding user response：

It can be done this way:

function onEdit(e){
  var sheetB = e.source.getActiveSheet();
  var sheetB_name = sheetB.getName();
  if (sheetB_name != 'SheetB') return;

  var sheetB_cell_value = e.value;
  var sheetB_cell_row = e.range.rowStart;
  var sheetB_cell_col = e.range.columnStart;
  
  var sheetA = e.source.getSheetByName('SheetA');
  var sheetA_cell = sheetA.getRange(sheetB_cell_row,sheetB_cell_col);
  var sheetA_cell_value = sheetA_cell.getValue();

  if (sheetB_cell_value != sheetA_cell_value) sheetA_cell.setBackground('#faec15');
}

CodePudding user response：

function onEdit(e){
  const sh = e.range.getSheet();
  if(sh.getName() == 'SheetB' ) {
    e.source.getSheetByName('SheetA').getRange(e.range.rowStart,e.range.columnStart).setBackground("#faec15");
  }
}