1

I want the color to show in the dropdown menuI have a sheet in which I have a color selection dropdown. Once selected, I have the cell conditionally formatted to change background color according to the selection. What I want to accomplish is to have the dropdown the user is selecting reflect the color they are choosing. In other words, not only would the dropdown show the word "Red", but the background of the selectable element would also be red.

I made my dropdown list from a named range using data validation, and conditional formatting to change the color of the cell corresponding to the selection. I was hoping that using conditional formatting on the named range would make the dropdown list appear as formatted from the named range. However, the dropdown list only shows the text, plain, unformatted. Anyone have any tricks to pass formatting through to the dropdown selection list?

AndyMM
  • 21
  • 3
  • 1
    Welcome to [Stack Overflow](https://stackoverflow.com/tour). See [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/a/261593/13045193) – doubleunary Nov 12 '22 at 15:47

1 Answers1

2

Use an onEdit(e) simple trigger, like this:

/**
* Simple trigger that runs each time the user hand 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. '
      + 'It runs automatically when you hand edit the spreadsheet. '
      + 'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  colorfulDropdownLists_(e);
}


/**
* Sets the color of a drop-down list cell to the color
* of the cell where the value was picked from.
*
* @param {Object} e The onEdit() event object.
*/
function colorfulDropdownLists_(e) {
  // version 1.0, written by --Hyde, 21 October 2022
  //  - see https://support.google.com/docs/thread/185026821
  //  - see https://stackoverflow.com/q/74413771/13045193
  const rule = e.range.getDataValidation();
  if (!rule || rule.getCriteriaType() !== SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) {
    return;
  }
  const dropdownRange = rule.getCriteriaValues()[0];
  const colors = dropdownRange.getBackgrounds();
  const values = dropdownRange.getDisplayValues();
  let color = null;
  values.some((row, rowIndex) => row.some((value, columnIndex) => {
    if (value === e.value) {
      color = colors[rowIndex][columnIndex];
      return true;
    }
  }));
  e.range.setBackground(color);
}
doubleunary
  • 13,842
  • 3
  • 18
  • 51
  • This is both helpful, and way over my head! Should simply pasting this into the script editor create the colorful dropdowns, or do I have to name the ranges and row & rowIndex in the script? – AndyMM Nov 12 '22 at 21:18
  • The script requires no configuration, so yes, you can just paste it over the placeholder code in **Extensions > Apps Script**, save and close. The drop-downs in your spreadsheet will then copy the cell fill color of the source cells as you choose values. See [Apps Script at Stack Overflow](https://stackoverflow.com/tags/google-apps-script/info). – doubleunary Nov 12 '22 at 22:25
  • It appears that this accomplishes only the same thing that I'd already accomplished by conditional formatting my color choices, in some ways more efficiently, but in other ways less so. However, what I'm trying to accomplish is not simply change the color of the cell after selection, but the color of the selection itself in the dropdown. – AndyMM Nov 12 '22 at 23:01
  • To change "the color of the selection itself in the dropdown", use Google Sheets built-in **Data Validation** feature. There is a color selector right next to each option in the list. – doubleunary Jun 07 '23 at 20:46