How to apply conditional formatting based on a cell color?-CodePudding

How do I use a vlookup function in conditional formatting to find out color associated with the category.

I have two columns in the settings tab: first one has a list of categories and the next column has cell with background color in them which is supposed to be associated with the selected category.

In the main sheet, user can select the category from the dropdown. How do I have conditional formatting apply the background color to this value based on the associated color found in the settings tab.

sheet:

CodePudding user response：

In your situation, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. And, when you want to use this script, please change the dropdown list of the column "B" in the sheet "MAIN". By this, the script is run and the background color is changed using the values of the sheet "SETTINGS".

function onEdit(e) {
  const {range, source, value} = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "MAIN" || range.columnStart != 2 || range.rowStart == 1) return;
  const settingSheet = source.getSheetByName("SETTINGS")
  const r = settingSheet.getRange("B5:C"   settingSheet.getLastRow());
  const colors = r.getBackgrounds();
  const obj = r.getValues().reduce((o, [b], i) => (o[b] = colors[i][1], o), {});
  range.setBackground(obj[value] || null);
}

Note:

This sample script is run by a simple trigger. So, when you directly run the script at the script editor, an error like Cannot destructure property 'range' of 'e' as it is undefined. occurs. Please be careful about this.

Sample script:

Note:

References: