different color per row

Tobias Krause

Hi all,

I think I found a solution which allows to work with a column formula. That is much easier and more reliable if you frequently have to add new rows to the sheet.

So I use two columns. One AutoNumber column (in my example called "Auto") and a Color column with the column formula in it. And the formula is:

=IF(ISODD(MATCH(Auto@row, Auto:Auto, 0)), 0, 1)

This formula checks the current position of the row in the sheet. If that position is Odd, the result is "0", if the position is even the result is "1".

I did not try out filtering yet. But this did the trick for me and might help others as well!

KDavis_WF

The ongoing issue with this overall implementation is the conditional formatting rules have a higher priority than sheet formatting. For example, if the conditional formatting is set so even rows are unchanged and odd rows are light gray, I can no longer highlight a cell by changing the background color of an individual cell in an odd row.

One option is to change the font color, but only if the font color isn't defined in the conditional formatting rules.

I'm finding a lot of workarounds needed for a basic layout requirement.