How to highlight cells red in a range if the same data exists in another range

Question

I am working on one sheet called 'TEAM SETUP'

I have a list of names in cell range: B29:E39 which is a range I drop names into as staff on 'Holiday'

I am then dropping names (deploying staff) into different roles in range: B2:S27

I want to highlight any cells in range B2:S27 where the values also exist in B29:E39 to flag to me that I've deployed a staff member that is 'on holiday'

Whether this is achievable with a script or conditional formatting I would be super appreciative if anyone can help me out with this.

Answer 1

It is fairly straight forward when doing it via script. See my script below:

Code:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();

  // Define ranges to only continue the function when edited cell is in there
  // This is to prevent unnecessary execution time as we have quotas
  var dataRange = { // B2:S27
    top : 2,
    bottom : 27,
    left : 2,
    right : 19
  };
  var holidayRange = { // B29:E39
    top : 29,
    bottom : 39,
    left : 2,
    right : 5
  }
  // if edited cell is in data or holiday range
  if(isInRange(e.range, dataRange) || isInRange(e.range, holidayRange)) {
    var data = sheet.getRange("B2:S27").getValues();
    // To filter only non blank cells, add filter(Boolean)
    var holiday = sheet.getRange("B29:E39").getValues().flat().filter(Boolean);

    data.forEach(function (row, i) {
      row.forEach(function (cell, j) {
        if (holiday.includes(cell))
          sheet.getRange(i + 2, j + 2).setBackground("red");
        // set to white if it turns out the value is now not found
        else
          sheet.getRange(i + 2, j + 2).setBackground("white");
      });
    });
  }
}

function isInRange(cell, range) {
  var startRow = range.top;
  var endRow = range.bottom;
  var startColumn = range.left;
  var endColumn = range.right;
  return cell.getRow() >= startRow && cell.getRow() <= endRow &&
         cell.getColumn() >= startColumn && cell.getColumn() <= endColumn;
}

Output:

Reference:

• includes
• setBackground