Google-sheets – How to change the background color of a cell based on the cell contents using a color lookup sheet

conditional formattinggoogle sheets

Let's say in Sheet 2 I have a column (A) of words, and a column (B) of hex color codes. In Sheet 1, wherever a word from Sheet 2 appears alone in a cell, that cell should get the background color from Sheet 2.

EX. Sheet 2

A | B
Cat | #FF2223
Dog | #114589
Bat | #123456

I've tried using custom functions, but custom functions don't give you permissions to set the background color of cells.

Using conditional formatting seems awkward, as I might have thousands of words in Sheet 2.

Is there a way to accomplish this?

Best Answer

Without a script

You may have thousands of words, but it's not practical to have thousands of colors in your spreadsheet, unless you are writing a color vision test. Most people can't reliably tell the difference between subtly different colors, especially on a computer screen. So, if you use a reasonable number of colors (e.g. 50) and group the words on Sheet 2 in 50 columns accordingly, then 50 conditional rules would do the job: each would be a custom formula like 

=match(A1, indirect("Sheet2!C1:C"), 0)

where A1 is the upper-left corner of the range to be formatted on Sheet1, and C is one of the word columns on Sheet2.

With a script

If you insist on having thousands of colors, then a script below can do the job. It does the following:

  1. Runs on every edit
  2. Checks that the edit is to Sheet1
  3. Grabs the data from Sheet2
  4. Looks until either there is a match or the table ends.
  5. Sets cell background (white if no match).
function onEdit(e) {
  if (e.range.getSheet().getSheetName() == 'Sheet1') {
    var values = e.source.getSheetByName('Sheet2').getRange('A:B').getValues();
    var color = '#ffffff';
    var i = 0;
    while (i < values.length && values[i][0]) {
      if (values[i][0] === e.value) {
        color = values[i][1];
        break;
      }
      i++;
    }
    e.range.setBackground(color);
  }
}
Related Topic