9

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. Mar 24 '16 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.
    – Rubén
    Mar 24 '16 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
    Mar 25 '16 at 2:50
5

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
    Mar 27 '16 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
    Nov 2 '17 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.
    – Rubén
    Nov 2 '17 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
    Aug 19 '20 at 6:03
12
=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".

2
  • This is an awesome solution!
    – hitzg
    Feb 18 at 16:38
  • +10 for a brilliant insight!
    – Shanti
    Apr 29 at 19:56

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.