Search code examples
google-apps-scriptgoogle-sheetsscripting

google sheets active cell highlight and un highlight concern that you have with clearing the format of the cell within the code

where active cell will highlight matchings cell on rest of sheet once its move to anther cell then old highlight will be back to Normal and new cell match will highlight, as well let me know in scripts that where I Can change Column change if i want to move from A and B to F V Column

where active cell will highlight matchings cell on rest of sheet once its move to anther cell then old highlight will be back to Normal and new cell match will highlight, as well let me know in scripts that where I Can change Column change if i want to move from A and B to F V Column

Test File

Solution

  • I believe this question is in relation to this post: auto highlight active cell find duplicates google sheets, and you would like to clear the contents of the other cells that are no longer relevant to the new one you'll select in column B.

    You may add this code to the Google Apps Script currently implemented to accomplish what you would like.

    ss.getRange(5, 1, ss.getLastRow(), ss.getLastColumn()).setBackground('#FFFFFF');

    The completed code would become:

    function onSelectionChange(e) {
  var range = e.range;
  var row = range.getRow()
  var col = range.getColumn()
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = ss.getRange(row, col).getValue();
  var highlight = ss.getRange(1, 1, ss.getLastRow(), ss.getLastColumn()).getValues();
  ss.getRange(5, 1, ss.getLastRow(), ss.getLastColumn()).setBackground('#FFFFFF');
  if (col == 2) {
    highlight.forEach((x, a) => {
      x.forEach((y, b) => {
        if (y.toString().includes(cell)) {
          ss.getRange(a + 1, b + 1).setBackground('#90EE90');
        }
      })
    })
  }
}

    OUTPUT

    OUTPUT

    REFERENCE