How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets?

Question

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.

Answer 1

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

• Extend Google Docs, Sheets, and Forms with Apps Script
• Install, use & uninstall add-ons

Answer 2

=sparkline(1,{"charttype","bar";"color1",A1})

This will provide a solid colored cell using the Hex color code in A1.

Also accepts some named colors as well, e.g. "green", "yellow".

Answer 3

This formula has worked for me:

=SPARKLINE(1, {"charttype", "column"; "color", A1})

Answer 4

I found this script (not mine) Google-sheets – Change Google Sheet cell background color based on hex value within does it slightly differently just with copy paste (no need to type the Hex values):

function onEdit(e) {
  r = e.range;

  if(r.getSheet().getSheetName() == "colors"){ //the sheet I want to apply this to is called colors

    var rows = r.getNumRows();
    var columns = r.getNumColumns();
    var colors = [] //this is our 2 dimensional array of colors in case you copy and paste a large amount of colors

    for (var i = 1; i <= rows; i++) { //go down each row
      var row = [] //create a new row array to clear the old one when we go down a row
      for (var j = 1; j <= columns; j++) { //then go across each column
        row.push(r.getCell(i,j).getValue()) //put together the row of colors
      }
      colors.push(row); //insert our row of colors so we can go down the next row
    }
    r.setBackgrounds(colors) //batch update in case you update many colors in one copy and paste otherwise it will be very slow
  }
}

Don't forget to rename the sheet's tab "colors":

1000 cells sample sheet

Also here's a new script version by @Tanaike to inverse the font colors based on the cells background colors (to improve readability).