Google Sheets – Change Cell Color to Hexadecimal Value in Google Spreadsheets

conditional formattinggoogle sheetsgoogle-apps-script

Based on my data, I calculated values of hexadecimal color codes. I want some cell (I don't care whether it's an empty cell at the end of the row or the same cell) to be the color based on the code.

I don't want to use conditional formatting because it changes to a specific color or color gradient, not a specific hexadecimal value. I also have the RGB values if it is easier with those. This may not even be possible, but it would be really great.

I can change the color of each cell manually, but I will be updating the data which will update the hex value and I want the color to update automatically as well.

Best Answer

Short answer

Use Google Apps Script or a Google Sheets Add-on as there is no built-in feature to set cell background color by color codes.

Explanation

Google Apps Script is a tool that can be used to extended Google Sheets and other Google apps. It could be used to created add-ons.

There are two class methods that can be used to set the color of a single cell:

setBackground(color) : Sets the background color of all cells in the range in CSS notation (like '#ffffff' or 'white').
setBackgroundRGB(red, green, blue) : Sets the background to the given RGB color.

Below is an example of a script that sets the color of one cell based on the CSS color the cell at it's left when the value of that cell is edited.

/*
 * Sets the color of adjacent cell on Sheet1 when cells
 * of the column A are edited
 */
function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var col = range.getColumn();
  if(sheet != 'Sheet1' && col != 1) return;
  var color = e.value;
  range.offset(0, 1).setBackground(color);
}

Demo

References

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:

Runs on every edit
Checks that the edit is to Sheet1
Grabs the data from Sheet2
Looks until either there is a match or the table ends.
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);
  }
}

Best Answer

Short answer

Explanation

References

Related Solutions

Google Sheets Conditional Formatting – Format Range Based on Other Cell Values

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

Without a script

With a script

Related Topic