Friday, May 11, 2012

yogi_Color All Odd Rows Of A Sheet different Color Compared To The Nominal White BackGround Color Of Even Rows

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #535    May 11, 2012

user JoshuaMcEI said: 
Visual Asthetics of Spreadsheet Is there a way to have alternate rows shaded differently? For instance, I would like all "even" rows to be shaded different from the odd rows. 
The purpose of this would be to make it easier on the eyes when scrolling through data. 
My fellow Top Contributor Gill suggested to Joshua about using a script ... sure use of a script would be nice, however in the following I suggest using an approach based on native spreadsheet formulas, which I believe is preferable to using a script:
Let us say we want to color all odd rows as brown
1) use the following formula in row 1 of any column, say cell A1: =ArrayFormula(mod(row(YY:YY),2))
2) use the FILTER command to select all rows in column A for 1
3) with all rows selected, change the background color to brown
on removing the filter, all odd rows are colored brown and all even rows are the nominal background color white now select column A and clear the contents of column A
that should do it.
Please note in my following blog post using pretty much the approach I presented here I had posted a solution for coloring each of the odd and even rows different specific colors:
so without much further ado, here we go ...