2

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.

  • No, you can't use a formula to color a cell without conditional formatting. – Raystafarian 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
0

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

  • 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

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.