Google Spreadsheet: Changing cell colours if one or multiple text variables exist

244 Views Asked by At

I'm a newbie hacking together a script to change the background colour of specifics cells based upon text entry in a master column.

Basically where multiple country codes 'xx', 'xx' exist in row B for example, how can I, in a simpler way, highlight those fields in the corresponding rows which relate to those country codes? Currently it functions where a single locale is included in row a but not with multiples.

I have this working but only for one variable at a time (Example text is: AU) .

I need this get this to work for multiple text variables. (Example text is: "AR, AU, BEfr")

Column B: - Locale column
Column I: - AR column
Column J: - AU column
Column K: - BEfr column
Column L: - BEnl column
Column M: - BR column
etc....

My current code below. Certainly the long way to do this I imagine. I have named ranges selected called "Locales" for the locale column and "Checklist" for all the checkboxes.

Help is much appreciated.

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('R186');
var values1Rule1 = s.getRange('Locales').getValues();
var range3Rule1 = s.getRange('AU');
var acro1 = 'AR', acro2 = 'AU', acro3 = 'BEfr', acro4 = 'BEnl', acro5 = 'BR';   
var color1 = '#ADD8E6';


for (var row in values1Rule1) {
for (var col in values1Rule1[row]) {

if (values1Rule1[row][col] == acro1) s.getRange(s.getRange('AR').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else if (values1Rule1[row][col] == acro2) s.getRange(s.getRange('AU').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else if (values1Rule1[row][col] == acro3) s.getRange(s.getRange('BEfr').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else if (values1Rule1[row][col] == acro4) s.getRange(s.getRange('BEnl').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else if (values1Rule1[row][col] == acro5) s.getRange(s.getRange('BR').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else s.getRange(s.getRange('locales').offset(row, 0, 1, 58).getA1Notation()).setBackgroundColor('white').setFontColor('black'); }
}};
1

There are 1 best solutions below

0
On

This may be something addressed in new Google Sheets with Format, Conditional formatting..., Custom formula is:

=$B1=I$1  

say applied to Range I:M:

SO20684892 example

or may just be I am misunderstanding.