Conditional Formula to alternate the fill color of rows

[TraciAnn]

I used to use a Mod function in Conditional Formulas to alternate the fill
color of rows (like a checking journal) to make it easier to read, but I
can't remember the full syntax.

Does anyone know?

Thanks!

 

[Bob Umlas]

Hey, Harald -- you trying to become an MVP again?

 

[Harald Staff]

Hi Bob

That would certainly be something. No, just using Excel more these days.
Even beginning to prefer the 2007 version as my default, which I thought
would not happen, it took no more than three and a half year to get there
<bg>

Best wishes Harald

 

[T. Valko]

Even beginning to prefer the 2007 version

It's not too bad if you keep the ribbon minimized.

--
Biff
Microsoft Excel MVP

Hi Bob

That would certainly be something. No, just using Excel more these days.
Even beginning to prefer the 2007 version as my default, which I thought
would not happen, it took no more than three and a half year to get there
<bg>

Best wishes Harald

 

[Chip Pearson]

See http://www.cpearson.com/Excel/banding.aspx . This describes the
formula to include any number of rows in each color block, rather than
restricting it to alternating cells. For example, you color banding
may be 3 rows colored and then 3 rows plain.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[TraciAnn]

Thanks Chip! That was wonderful.

In the meantime I came along another post that didn't come up in previous
searches do to UK spelling of "colour". I also liked a solution provided by
Pete_UK. The neat thing about his function is that the conditional formatting
does not get applied unless their is data in the row.

Here's his function if it is of any use:
==============================
I used a slightly different formula in your step 3:

=AND(MOD(SUBTOTAL(3,$A2:$A$2),2)=0,$A2<>"")

so that the conditional formatting is not affected by blanks in column
A. The banding shows when you have data in column A, alternately white
and yellow - empty cells are always white, so you can have areas of
white with a block of empty cells. When you apply the filter you get
alternate rows coloured. The effect is more noticeable if you set up a
second condition (with say a green background) with a formula like:

=AND(MOD(SUBTOTAL(3,$A2:$A$2),2)=1,$A2<>"")

Maybe you would like to look into this effect.

Hope this helps.

Pete
==============================

Thanks again!

 

[TraciAnn]

Thanks Harold!

I like the simplicity of this one, however, my needs were a little more
complex. So the formulas offered by Chip and Pete_UK were better suited.

Thank you for your help!