Set a cell background color based on a HEX value from another cell

Question

From this question: Set the background color to a cell based on a hex value

I'm looking for a formula that I can write down in a cell then in an adjacent cell appears the HEX color I wrote down. And that color stays there.

They answered with a script that changes everything in the Google sheet. It's not what I'm looking for because it edits the whole document. And when I delete the HEX code from the cell, the color is deleted too.

I found another answer: How to change the color of a cell based on rgb data in other cells

The result is what I'm looking for but the code won't work for me because a) they're in RGB and 2) they work onEdit and I already have an onEdit function in my Google sheet.

I understand I can use Conditional Formatting, or I can go to the palette and painstainkingly write down each one of the HEX codes.

But what I've been looking for is: a very succinct formula that works when I write it down in a very specific cell or specific column or specific row after I copypaste the custom function for that succinct formula in Apps Script, of course.

Just like this one but in reverse, lol How to get the hexadecimal codes of colors in google sheets

I'm self-learning -how to copypaste- code, I just want things to work out as I want.

Answer 1

write down in a cell then in an adjacent cell appears the HEX color I wrote down

That cannot be done with a formula, but it can be done with an onEdit(e) script, like this:

'use strict';

/**
* Simple trigger that runs each time the user manually edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) throw new Error('Please do not run the onEdit(e) function in the script editor window.');
  setHexColorInNextCell_(e);
}


/**
* Changes the background color of a cell when a hex color code is entered in the cell to its left.
*
* @param {Object} e The onEdit() event object.
*/
function setHexColorInNextCell_(e) {
  // version 1.0, written by --Hyde, 21 July 2024
  //  - see https://stackoverflow.com/q/78773946/13045193
  try {
    const parameters = [

      ////////////////////////////////
      // [START modifiable parameters]
      {
        sheetsToWatch: /./i,
        sheetsToExclude: /^(Master)$/i,
        patternsToWatch: [/#[a-f\d]{6}/i], // hex color codes are like #00ff00
      },
      // [END modifiable parameters]
      ////////////////////////////////

    ];
    const value = e.value || e.range.getDisplayValue();
    if (!value) return;
    let sheet, sheetName;
    const settings = parameters.find(p =>
      (!p.patternsToWatch.length || p.patternsToWatch.some(p => value.match(p)))
      && (sheetName = sheetName || (sheet = sheet || e.range.getSheet()).getName()).match(p.sheetsToWatch)
      && (!p.sheetsToExclude || !sheetName.match(p.sheetsToExclude))
    );
    if (!settings) return;
    const nextCell = e.range.offset(0, 1);
    nextCell.setBackground(value);
  } catch (error) {
    e.source.toast(`${error.message} ${error.stack}`, 'Error in setHexColorInNextCell_()', 30);
    throw error;
  }
}

The script will run automatically as you enter hex color codes like #ff0000 in a cell.

I already have an onEdit function in my Google sheet

Rename your current onEdit() function to something like anotherOnEdit_() and add it to the onEdit(e) function above, like this:

  // ...
  setHexColorInNextCell_(e);
  anotherOnEdit_(e);
}

See Simple triggers and onEdit(e) best practices.