How to determine the start and end cells of a colored range in Google Sheets?

Other Questions Google Sheets
1

I’m working on a project management timeline in Google Sheets. My sheet looks like this:

| Task | Start | End | Jan | Feb | Mar | Apr | May | Jun |
|------|-------|-----|-----|-----|-----|-----|-----|-----|
| Task1|       |     |     | ### | ### | ### |     |     |
| Task2|       |     |     |     | ### | ### | ### |     |

I need help creating two custom functions. These functions should take a range (like E2:J2) and the task name (A2) as inputs. They should then figure out where the colored cells start and end, and use that to fill in the Start and End columns.

I’m not sure how to write these functions. Any ideas on how to detect the first and last colored cells in a row? Thanks for your help!

2

I’ve tackled a similar challenge in my project management work. Here’s what worked for me:

Instead of relying on cell colors, I found it more reliable to use a specific character (like ‘X’ or ‘#’) to mark active periods. This approach is more robust and easier to manipulate with formulas.

For the start date, you can use:
=ARRAYFORMULA(INDEX(B$1:G$1, MATCH(TRUE, B2:G2<>“”, 0)))

For the end date:
=ARRAYFORMULA(INDEX(B$1:G$1, MAX((B2:G2<>“”)*COLUMN(B2:G2))))

These formulas look across the row, find the first and last non-empty cells, and return the corresponding month from the header row.

This method has been more reliable for me than trying to detect colors, which can be tricky in Sheets. It also makes it easier to update and maintain your timeline as the project evolves.

3

While color-based detection can be tricky, there’s a workaround using Google Apps Script. Here’s a custom function approach:

function getColoredRange(range, color) {
  var values = range.getBackgrounds();
  var start = 0, end = 0;
  for (var i = 0; i < values[0].length; i++) {
    if (values[0][i] == color) {
      if (start == 0) start = i + 1;
      end = i + 1;
    }
  }
  return [start, end];
}

Use it like: =getColoredRange(E2:J2, “#ffff00”) for yellow cells.

For start/end dates, combine with INDEX/MATCH:
=INDEX($D$1:$I$1, 1, getColoredRange(E2:J2, “#ffff00”)[0])

This solution maintains your color-based approach while providing the functionality you need.

4

hey sophia, i’ve got a trick for u. instead of colors, try using conditional formatting to fill cells based on a simple formula like =IF(A2=“x”,TRUE,FALSE). then u can use MATCH() to find start/end:

=MATCH(TRUE,B2:G2,0) for start
=MATCH(TRUE,B2:G2,0)+COUNTIF(B2:G2,TRUE)-1 for end

works like a charm! lemme know if u need more help