I'm using banding to highlight some areas, with a section in between with a fixed colour. It should look like this.
Sometimes, when I enter a number in the blue area in the middle, the banding appears:
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?
Best Answer
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?