Home > Mobile >  Copy / paste background color from one column to another sheet in a column with matched values
Copy / paste background color from one column to another sheet in a column with matched values

Time:08-24

In a sheet I have a column with values and background colors. In another sheet, I have a column with the same values but in a random order.I would like a script that would paste the background color of the column from the first sheet into the column in the second sheet if their values match.

Source color:

Source color

Wished result:

wished result

enter image description here

Result: enter image description here


From your code I just modifed the destination row and column since you have the values fixed starting from row 4 and in column L. You don't need the Dynamic range getDataRange() used in your code as it will impact all the other cells in the Data Range.

Suggestion:

You can also add the code below to your onEdit()

  if (col == 12) {
    var value = range.getValue();
    var validationSheet = ss.getSheetByName("VALIDATION");
    var optionColor = validationSheet.createTextFinder(value).findNext().getBackground();
    range.setBackground(optionColor);
  }

Final Code:

function onEdit(e) {
  var ss = e.source;
  var sourceSheet = ss.getActiveSheet();
  var shName = sourceSheet.getName();
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();

  var destSheet = ss.getSheetByName("COMMENTAIRES");

  if ((shName == "DATA" || shName == "EPHAD" || shName == "LIVRET") && (row > 4 && col >= 12 || col <= 16)) {
    var dataToCopy = range.getValue();
    var email = sourceSheet.getRange(row, 8).getValue();
    var destRow = destSheet.createTextFinder(email).findNext().getRow();
    destSheet.getRange(destRow, col).setValue(dataToCopy);
  }

  //ADDED SECTION
  if (col == 12) {
    var value = range.getValue();
    var validationSheet = ss.getSheetByName("VALIDATION");
    var optionColor = validationSheet.createTextFinder(value).findNext().getBackground();
    range.setBackground(optionColor);
  }
}

enter image description here

Result:

enter image description here

  • Related