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

Question

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.

Answer 1

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(Σ)))))