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'); }
}};
This may be something addressed in new Google Sheets with Format, Conditional formatting..., Custom formula is:
say applied to Range
I:M
:or may just be I am misunderstanding.