Change Background Color When Cell Contains That Colors Name

Question

I have a Google Sheet document that I use as a schedule for work. I am currently using the following script to sort and change the entire rows background depending on the number in column "K"; however, I am now wondering how I can change the background color of only column "I" depending on what is typed in there. So for example if I type "Red" into that column, I want the background of only that individual cell to change to red.

I know this is easily done using conditional formatting, however I am constantly adding new lines to this sheet and moving others to another page, so conditional formatting quickly looses which range to correctly apply it to (if that makes sense)

Thank you!

function onEdit(e) {
  if(e.range.columnStart === 11) {  
    // color the row according to the color codes and logic shown below 
    var refs = [14, "#93c47d", 15, " #00ffff", 16, "#f4c432", 17, "#ff00ff", 20, "#ffff00", ,"#ffffff"], index;

    if(e.value >= 1 && e.value < 14) {
      index = 0;
    } else if(refs.indexOf(Number(e.value)) !== -1) {
      index = refs.indexOf(Number(e.value));
    } else {
      index = refs.length - 2;
    } 

    var r = e.range.rowStart, A1 = r + ":" + r;
    e.source.getRange(A1).setBackground(refs[index + 1]);

    // sort the range after coloring
    e.source.getRange("A:K").sort({column: 11});  
  }
}

Tiffany G. Wilson Tiffany G. Wilson · Accepted Answer · 2016-08-10T23:37:22

The first thing I would do after entering your if is make your refs an object instead of an array:

var refs = {
 green: "#93c47d",
 aqua: "#00ffff",
 orange: "#f4c432",
 magenta:"#ff00ff",
 yellow: "#ffff00"
};

Then you can get the color you want based on the existence of the key in your object, i.e. when you type 'orange' you pull the color value associated with it, #f4c432. That way you don't have to remember color codes.

var color;
if(refs[e.value]){  // If this key exists, use its value
  color = refs[e.value];
} else {
  color = "#ffffff";  // Default value
}

Finally you can carry out setting the color of the row:

var row = e.range.getRow();
e.source.getRange(row,1,1,e.source.getMaxColumns()).setBackground(color);

And finish with your sorting.

Change Background Color When Cell Contains That Colors Name

1 Answers