I wrote a formula that attempts to create a checkerboard pattern via conditional formatting, that should even work with Merged Cells. However, it seems to fail in an almost undebuggable manner with seemingly random outputs; is this a Google Spreadsheet bug or am I just missing something here?
Conditional formatting formula:
OFFSET(A1,ROW(K5)-1,0,1,COLUMN(K5))-- this will give you
COUNTIF(..., "<>")-- this will count all non-empty cells in a range
ISODD-- equivalent to mod 2, this will turn
Goal: In a given row, swap color whenever we encounter a new non-empty cell.
The idea is that each cell counts from the left to see how many non-blank values there are inclusive of itself; whenever a new value is added to the row, the color should flip for that value and everything to the right of it. This will reduce to a checkerboard pattern but will handle merged cells correctly (since they're technically there but are blank squares).
However, when I try to implement this, even piecemeal, the result seems almost random.
(If anyone else was thinking of doing this: My current workaround to get something similar is to make a random array of colors by pumping the row and column through a very simple hash function, probabilistically coloring some fraction of the outputs a random color over the course of a few independent formulae.)
example row (
^^^^ indicates true/shaded, otherwise false/unshaded):
[ 1 ][ 2 ][ a ][ 4 ][ ][ z ][ 7 ][ q ][ ][ ][ 8 ][ ][ 9 ][ ] ^^^^^ ^^^^^ ^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^ [ 1 ][ 2 ][ a ][ 4 mrgd ][ z ][ 7 ][ q merged ][ 8 mrgd ][ 9 mrgd ] ^^^^^ ^^^^^ ^^^^^ ^^^^^^^^^^^^^^^ ^^^^^^^^^^