# Conditional Formatting formula for alternating checkerboard with Merged Cells?

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:

``````=ISODD(COUNTIF(OFFSET(A1,ROW()-1,0,1,COLUMN()),"<>"))
``````

Formula breakdown:

• `OFFSET(A1,ROW(K5)-1,0,1,COLUMN(K5))` -- this will give you `A5:K5`
• `COUNTIF(..., "<>")` -- this will count all non-empty cells in a range
• `ISODD` -- equivalent to mod 2, this will turn `[1,2,3,4,5,...]` into booleans `[T,F,T,F,...]`

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 ]
^^^^^     ^^^^^          ^^^^^     ^^^^^^^^^^^^^^^          ^^^^^^^^^^
``````
• can you share a copy of your sheet with example of desired output? – user0 Jul 17 '19 at 4:55