How to auto colour multiple cells based on specific text in Google Sheet - Stack Overflow

This is for coaches to reserve a time slot.

I don't know if there is a conditional format I can use to achieve this but if a coach has a 30 min lesson he needs to book on the schedule sheet, he will write his name with 30 after indicating the duration of his lesson.

Instead of him filling another cell just down below, I would like some kind of format that will automatically fill it with a colour. It does not need to be the same colour as his original colour.

If it's 45 min lesson, 2 cells below from his name will be filled with colours. I have 6 teachers. I was thinking maybe the number after coach's name could be the indication for how many cells that need to be filled with colours.

Thank you.

This is for coaches to reserve a time slot.

I don't know if there is a conditional format I can use to achieve this but if a coach has a 30 min lesson he needs to book on the schedule sheet, he will write his name with 30 after indicating the duration of his lesson.

Instead of him filling another cell just down below, I would like some kind of format that will automatically fill it with a colour. It does not need to be the same colour as his original colour.

If it's 45 min lesson, 2 cells below from his name will be filled with colours. I have 6 teachers. I was thinking maybe the number after coach's name could be the indication for how many cells that need to be filled with colours.

Thank you.

• google-sheets

Share Improve this question Follow edited Jan 31 at 21:05 R.K. asked Jan 30 at 22:15 R.K.R.K. 2966 bronze badges 4

• 1 Would you be able to provide your sample sheet, with your initial output, and also your expected output so that we can further help you. You may use this to provide a markdown table (you may create one with the help of this link – Jats PPG Commented Jan 30 at 22:34
• 1 Hi there, I added a image of what I would like to achieve. Thank you. – R.K. Commented Jan 31 at 2:40
• 4 auto colour multiple cells based on a specific text May I suggest that the question lacks detail. For example, what colour(s), what text is one extra cell, what text if two extra cells, what do you intend by "auto" - a function or a script? – Tedinoz Commented Jan 31 at 9:48
• I added more description to what I would like to achieve. I honestly not sure if it can be done by a function or a script. – R.K. Commented Jan 31 at 21:07

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 2

Assuming the values are in the range C2:F, you can create a helper column with the following formula:

=ARRAYFORMULA(REDUCE(TOCOL(,1),C2:F,LAMBDA(a,c,VSTACK(a,IF(c="",TOCOL(,1),ADDRESS(SEQUENCE(REGEXEXTRACT(TRIM(c),"\d+$")/15,1,ROW(c)),COLUMN(c)))))))

Then apply the following conditional formatting custom formula to C2:F:

=COUNTIF($A:$A,CELL("address",C2))

Where $A:$A is the helper column

.

Here's one approach you may test out; the formula is for the given range setup in the screenshot. You may need to adjust it accordingly for your sheet:

=index(let(Σ,xlookup("?*",to_text(B$2:B2),B$2:B2,,2,-1),xmatch(cell("address",B2),address(row(Σ)+sequence(choosecols(split(Σ," "),-1)/15,1,0),column(Σ)))))

The goal is to colour the cell that contains "Tom 30" and its cell below, as well as colour the cell that contains "Tom 45" and also colour 2 cells below.

It should be possible to Use custom formulas with conditional formatting as well as Google Apps Script's onEdit(e) if you'd like to auto colour multiple cells based on a specific text.

Given that the range you'd like to apply the custom formula to is A:~, you may use this conditional formatting rule for "Tom 30":

=OR(A1="Tom 30", IFERROR(IF(OFFSET(A1, -1, 0)="Tom 30", 1, 0), 0))

For the same range in "Tom 45", you may + Add another rule and use:

=OR(A1="Tom 45", IFERROR(IF(OFFSET(A1, -1, 0)="Tom 45", 1, 0), 0), IFERROR(IF(OFFSET(A1, -2, 0)="Tom 45", 1, 0), 0))

The custom formulas can also be combined into a single formatting rule:

=OR(A1="Tom 30", IFERROR(IF(OFFSET(A1, -1, 0)="Tom 30", 1, 0), 0), A1="Tom 45", IFERROR(IF(OFFSET(A1, -1, 0)="Tom 45", 1, 0), 0), IFERROR(IF(OFFSET(A1, -2, 0)="Tom 45", 1, 0), 0))

If you'd like to use Google Apps Script for this, here's an example script that should achieve what you'd like:

const onEdit = (e) => {
  var sh = e.source.getActiveSheet();
  var rg = e.range;
  var vl = rg.getValue();
  vl == "Tom 30" ? rg.setBackground("#BCD5AC") && sh.getRange(rg.getRow() + 1, rg.getColumn()).setBackground("#BCD5AC") : null;
  vl == "Tom 45" ? rg.setBackground("#9DC284") && sh.getRange(rg.getRow() + 1, rg.getColumn()).setBackground("#9DC284") && sh.getRange(rg.getRow() + 2, rg.getColumn()).setBackground("#9DC284") : null;
}

Note: The background colors in the code are the ones from the initial sample image.

UPDATE

The question now states that the functionality is for coaches to reserve a time slot. To apply the custom formula to 6 teachers, you may use:

=OR(ISNUMBER(SEARCH("30", A1)), IFERROR(ISNUMBER(SEARCH("30", OFFSET(A1, -1, 0))), 0), ISNUMBER(SEARCH("45", A1)), IFERROR(ISNUMBER(SEARCH("45", OFFSET(A1, -1, 0))), 0), IFERROR(ISNUMBER(SEARCH("45", OFFSET(A1, -2, 0))), 0))

Note: This only checks the number after the coach's name for 30 and 45. From what I see, @z.. and @rockinfreakshow's answer is dynamic if many cells would still need to be filled with colours.

If you'd like to use Google Apps Script for this, here's a modified version of the script that should achieve what you'd like:

const onEdit = (e) => {
  var sh = e.source.getActiveSheet();
  var rg = e.range;
  var vl = rg.getValue();
  var num = parseInt(vl.match(/(\d+)$/));
  if (num) {
    rg.setBackground("#6D9EEB");
    for (var i = 0; i < num / 15; i++) {
      sh.getRange(rg.getRow() + i, rg.getColumn()).setBackground("#6D9EEB");
    }
  }
}

Note: The code is dynamic and the background colors in it are the ones from the sample image.

REFERENCES

• Use conditional formatting rules in Google Sheets
• Simple Triggers