1

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: https://docs.google.com/spreadsheets/d/1UmHU76ZOoHhB-8HyHrY2ESZAjUpTq2FKkGFmGXNEQ2o/edit?usp=sharing

Improve this question
4
  • share a copy of your sheet Commented Nov 16, 2021 at 23:55
  • @player0 one moment please Commented Nov 17, 2021 at 0:17
  • @player0 sheet added in the description Commented Nov 17, 2021 at 0:25
  • referencing background color can be done only with script Commented Nov 17, 2021 at 0:29

1 Answer 1

Reset to default
2

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.

References:

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Can you please help me with this again. Thank you. stackoverflow.com/questions/71007154/…