Conditional Formatting conundrum

[Eloise T]

Why does changing the Conditional Formatting from this:
=MOD(ROW(),2)=0 [Note the 2…

To this:
=MOD(ROW(),3)=0 ...changed to 3]

...change the spreadsheet from this (Note blue background) on headings line:



To this with a yellow highlighted headings line:


Changing the 2 to 3 in the Conditional Formatting formula is only supposed to change the blue background from every other line, to every third line. In fact, changing the 2 to any other number changes the heading line to yellow (within the limits of the Condition Format). See attachment.

I'm using Excel 2007 if that matters.
Thanks for looking.

 

[pecoflyer]

This is perfectly correct, every row that is a multiple of 3 is formatted
If you want to format every third starting from the header row try =AND(ROW()>2,MOD(ROW()-2,3)=0)

 

[SirJB7]

Hi, Eloise_T!
As pecoflyer said Excel behaves as expected. Note that you're applying CF to range =$A$1:$W$100 so you have to take care of leaving the 1st row (or all header) out of the range or define carefully to which rows no. will the rule be applied to.
Sometimes is useful to assign a different format to title rows.
Regards!

 

[pecoflyer]

A small variation =(ROW()>2)*(MOD(ROW()-2,3)=0)

 

[NARAYANK991]

Hi ,

See the attached file.

This will now work correctly what ever be the number you use in the MOD function.

Narayan

 

[Eloise T]

Thanks to all of you (Narayank991, pecoflyer, and Sir JB7) who replied. Good information!