CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
RC0993
RC0993

Reputation: 968

Google Sheets' macro for changing a cell background color depending on input text

I want to make a macro in Google sheet for the following case

In a worksheet column, say column J, The allowed text inputs are "A", "B" and "C".

As soon as I put A, B or C (in the Jth column) the cell background color should change to "Green", "Yellow" & "Red" respectively.

How can I do that?

I don't know much about macros, I just got the task to do so. Can I get some help?

Upvotes: 1

Views: 3875

Answers (1)

Tanaike
Tanaike

Reputation: 201503

  • You want to change the background color of the edited cell, only when "A", "B" and "C" are inputted to the column "J".
  • "Google sheet macro" in your title and tag is Google Apps Script.

If my understanding is correct, how about these 2 sample scripts? Please think of this as just one of several answers.

Sample script 1:

In this sample script, "OnEdit" event of the simple triggers is used.

Flow:

  1. Check the range of edited cell. If the range is the column "J", the script is run.
  2. Check the inputted value. If the inputted value is "A", "B" and "C", the background color is changed.
    • If the inputted value is NOT "A", "B" and "C", the value is removed.

Script:

Please copy and paste the following script to the script editor, and save it. Then, please put the value to the column "J".

function onEdit(e) {
  if (e.range.getColumn() == 10) {
    var colors = {A: "green", B: "yellow", C: "red"};
    if (Object.keys(colors).indexOf(e.value) > -1) {
      e.range.setBackground(colors[e.value]);
    } else {
      e.range.setValue("");
    }
  }
}

Note:

  • In this sample script, the script runs for all sheets in the Spreadsheet.
    • If you want to run the script for the specific sheets, please tell me.
  • This sample script supposes the case that one value is put in one cell.
    • If you want to put several values to several rows on the column "J", please tell me.

Sample script 2:

In this sample script, set both Data Validation and Conditional Format Rule to the column "J".

Flow:

  1. Set Data Validation
    • By this, only values of "A", "B" and "C" can be put to the column "J".
  2. Set Conditional Format Rule
    • By this, when a value of "A", "B" and "C" is put in a cell of the column "J", the background color is changed.

Script:

Please copy and paste the following script to the script editor, and save it. Then, run myFunction() on the script editor. In this script, the condition works by running the script only once.

function myFunction() {
  var rangeA1Notation = "J:J"; // Column "J"
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(rangeA1Notation);

  // Set Data Validation
  var rule = SpreadsheetApp.newDataValidation().withCriteria(SpreadsheetApp.DataValidationCriteria.VALUE_IN_LIST, [["A", "B", "C"], false]).setAllowInvalid(false).build();
  range.setDataValidation(rule);

  // Set Conditional Format Rule
  var rules = sheet.getConditionalFormatRules();
  rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo("A").setBackground("green").setRanges([range]).build());
  rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo("B").setBackground("yellow").setRanges([range]).build());
  rules.push(SpreadsheetApp.newConditionalFormatRule().whenTextEqualTo("C").setBackground("red").setRanges([range]).build());
  sheet.setConditionalFormatRules(rules);
}

References:

If these methods were not what you want, I apologize.

Upvotes: 2

Related Questions