Alternate color rows (Excel XP)

Topic

New Reply

Coloring alternate rows can be done with conditional formatting and is not too difficult. However instead of coloring every other line or every fourth line, is it possible to select a block and color the first four rows one color, then skip four rows and color the next four? Thus the color scheme goes four on and four off.

One of the auto-format options uses a two on, two off style. (I believe it is “List 2”). Can this be easily adjusted?

Reply | Quote

Replies

The free Excel add-in “Shade Data Rows” can shade by…
cell value
every nth row
every group of rows of nth size

Download from … http://www.realezsites.com/bus/primitivesoftware%5B/url%5D
No registration required.

Jim Cone
San Francisco, USA

Reply | Quote

Sure.

In the usual case, when you are colouring every “Nth” row, you test for MOD(RowNum, N) = 0 – in this case, test for MOD(RowNum,8) <4. The actual formula in the conditional formatting test would be:

=(MOD(CELL("row",E5),8) < 4)

In fact, this will leave the first three rows uncoloured, then begin alternating 4-row blocks. If you really need to get the first four uncoloured, use

=(MOD(CELL("row",E5) - 1,8) < 4)

Reply | Quote

Hi Dean,
As a matter of interest, why do you use:
CELL("row",E5)
rather than just:
ROW(E5)?

Reply | Quote

Ummm….

Because until two minutes ago, I didn’t know that the “ROW()” function existed…

Old dog; new trick!

Reply | Quote

Thank you very much to both responders. The addin worked with the benefit of a graphical interface. The formula for conditional formatting also worked exactly as stated. I will puzzle over the formula to try to understand exactly how it works. The details in the help file were too minimal to even suggest how to do this.

Thanks again.

Reply | Quote