3

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?

1

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);
  }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.