auto highlight active cell find duplicates google sheets

Question

Example

active cell will highlight match words in same sheet

active cell will highlight match words in same sheet

active cell will highlight match words in same sheet

active cell will highlight match words in same sheet active cell will highlight match words in same sheet

Answer 1

Since you've only provided a photo of your data, I've copied the first five to replicate what you'd like to accomplish.

You may use the following code as a reference for what you'd like to do:

function checkCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = ss.getActiveCell();
  var aCell = cell.getValue();
  var highlight = ss.getRange(1, 3, ss.getLastRow()).getValues();
  for (var i = 0; i < highlight.length; i++) {
    if (highlight[i].toString().includes(aCell)) {
      ss.getRange('C' + (i + 1)).setBackground('#90EE90');
    }
  }
}

OUTPUT

I also added a button to run the script automatically to make it easier for you.

UPDATE

I see that the value of F2 is located in column B. I've updated my code to only start highlighting the other cells when you click on a value in that column. You may use the following code as a reference for what you'd like to do:

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();
  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

REFERENCES

• Loops and iteration
• Simple Triggers