22

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.

3
  • No, you can't use a formula to color a cell without conditional formatting. Commented Mar 24, 2016 at 15:13
  • 2
    Welcome to Web Applications. Excel Online and Google Sheets, while they have several similar features, they are not the same. As the question is at this time, it is too broad. Please choose one, Excel Online or Google Sheets. Bear in mind that you could post another question for the other app. Commented Mar 24, 2016 at 15:39
  • I narrowed this down to Google Spreadsheets for now since that was what the question was tagged with. We can always change it around later if it doesn't get an answer. Thanks :)
    – jonsca
    Commented Mar 25, 2016 at 2:50

4 Answers 4

7

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

4
  • This was very helpful. I'm still working on getting it to work with the specifics of my project, but the setBackground is exactly what I needed.
    – Elizabeth
    Commented Mar 27, 2016 at 21:10
  • I copied your demo scheet, but I get TypeError: Cannot read property "range" from undefined. (line 6, file "Code") when I try to run the script
    – Zach
    Commented Nov 2, 2017 at 19:00
  • @Zach: That happens when the onEdit(e) function is ran directly by clicking on the Run button on the Script editor as the e object is empty. Commented Nov 2, 2017 at 19:57
  • I removed if(sheet != 'Sheet1' && col != 1) return; to get it to work on all cells (not just the first column in the first sheet).
    – ahorn
    Commented Aug 19, 2020 at 6:03
37
=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".

4
  • 2
    This is an awesome solution!
    – hitzg
    Commented Feb 18, 2021 at 16:38
  • 3
    +10 for a brilliant insight!
    – Shanti
    Commented Apr 29, 2021 at 19:56
  • 2
    This should be the accepted answer.
    – John
    Commented May 16, 2022 at 13:58
  • This is absolute genius, why have custom scripts when you dont need them Commented May 1, 2023 at 21:47
1

This formula has worked for me:

=SPARKLINE(1, {"charttype", "column"; "color", A1})
1
  • The solution is perfect to an 8-year-old question, but the answer could be edited so it is clearer.
    – Blindspots
    Commented Apr 6 at 0:04
0

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":

enter image description here

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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