How to stop banding from re-appearing on Google Sheet

I'm using banding to highlight some areas, with a section in between with a fixed colour. It should look like this.

enter image description here

Sometimes, when I enter a number in the blue area in the middle, the banding appears:

enter image description here

I have no idea why this happens. I keep re-applying a fixed colour to those cells, using a macro, but sometimes the banding just keeps coming back.

Any ideas?

Here is an editable document that shows the problem, putting a value in F3 re-introduces the banding in that section, but putting a value in F9 does not. I'm a bit baffled why the problem only affects column F as well, not E or G.

The cells that are pale blue should stay pale blue, and not pick up the orange and white colour banding when values are entered.

https://docs.google.com/spreadsheets/d/17zRq27A6_ow56WNfbidGL07hTrbXc4i4Oco-auTictQ/edit?usp=sharing

Here's another document that is not editable, in case someone messes up the first, request access and I will grant it, or save a copy in your Google Drive account.

https://docs.google.com/spreadsheets/d/1sFbm91dUQ0xJ4CpCnWD-DLw5qLR2RXdUCmON1_ayHmk/edit?usp=sharing

If you click on cell B2 and open up the "Alternating colours" dialog, it shows the area as overlapping column E. This is clearly wrong, and is a symptom of the problem. I should not be surprised that the banding reappears when I enter a value in cell E3.

However, if you click on cell B9 and open up "Alternating colours", it shows that the banding area does NOT overlap column E. Nonetheless, enter a value in cell E9 and the banding expands. This is the bug: why are the alternating colours expanding to cover column E when I enter a value in it?

Answers 2

  • Looks like I can fix this by editing the Alternating Colours properties, I don't think this functionality was as sophisticated when I added it, and I had not gone back and looked at it again. The banding in some areas was overlapping with the blue, but not in others.

    Hmmm, nope. Banding keeps extending to the right, covering the blue area. I have disabled the code that re-applies formatting.

    Ok I've finally fixed it. I had to add an extra column to the right of the first banding area and hide it. The macro that fills in values in the first blue column was causing the banding to expand to the right, and I have no idea why. I will attempt to create another test case.

    The sheet that I shared actually shows this behaviour perfectly.

    If you click on cell B2 and open up the "Alternating colours" dialog, it shows the area as overlapping column E. This is clearly wrong, and is a symptom of the problem. I should not be surprised that the banding reappears when I enter a value in cell E3.

    However, if you click on cell B9 and open up "Alternating colours", it shows that the banding area does NOT overlap column E. Nonetheless, enter a value in cell E9 and the banding expands. This is the bug: why are the alternating colours expanding to cover column E when I enter a value in it?


  • I have added a sheet ("Erik Help") that does not rely on your script at all. I set up two custom CF rules as follows:

    ORANGE (applied to B1:D13,H1:K13)

    =IF(ISEVEN(VLOOKUP(ROW(B1),FILTER(ROW($A:$A),NOT(ISNUMBER($K:$K))),1,TRUE)),ISODD(ROW(B1)),ISEVEN(ROW(B1)))

    BLUE (applied to E1:G)

    =ISNUMBER($K1)

    That's it. And there doesn't seem to be any problem. So my recommendation would be to delete your script and just use the custom CF rules above.

    How The Rules Work:

    ORANGE:

    FILTER creates a list of only the rows where there is no value in Column K (i.e., rows for main headers). Then VLOOKUP looks up each row number within that FILTERed list, finding the closest one backward to any current row. If that found row ISEVEN, then the current row will be colored if it ISODD and vice versa.

    BLUE:

    This equates to saying, "If there is any number value in Column K, color this row."


Related Questions