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:
Wished result:
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);
}
}
Result: