Custom 160 Row Highlighting

[binar]

Fellow Forum Members,
Can anyone out there PLEASE help me modify the formula below:
=AND(MOD(ROW(),10)<=5,MOD(ROW(),10)<>0)

So that it automatically highlight rows in the pattern outlined below:


Row 1 = White (HEADER ROW)

First group of 160 Rows Highlighted in Yellow

Second group of 160 Rows Highlighted in White

Third group of 160 Rows Highlighted in Yellow

Fourth group of 160 Rows Highlighted in White

Fifth group of 160 Rows Highlighted in Yellow

And so on.

I intend to use this formula as a Conditional Formatting Rule but after playing with it for a long time I'm unable to get color bands that alternate at 160 rows. Any help will be greatly appreciated. Thanks.

 

[T. Valko]

Fellow Forum Members,
Can anyone out there PLEASE help me modify the formula below:
=AND(MOD(ROW(),10)<=5,MOD(ROW(),10)<>0)

So that it automatically highlight rows in the pattern outlined below:


Row 1 = White (HEADER ROW)

First group of 160 Rows Highlighted in Yellow

Second group of 160 Rows Highlighted in White

Third group of 160 Rows Highlighted in Yellow

Fourth group of 160 Rows Highlighted in White

Fifth group of 160 Rows Highlighted in Yellow

And so on.

I intend to use this formula as a Conditional Formatting Rule but after playing with it for a long time I'm unable to get color bands that alternate at 160 rows. Any help will be greatly appreciated. Thanks.

Try something like this:

=MOD(CEILING(ROWS(A$2:A2)/16,1),2)

 

[T. Valko]

Try something like this:

=MOD(CEILING(ROWS(A$2:A2)/16,1),2)

Ooops!

16 should be 160

=MOD(CEILING(ROWS(A$2:A2)/160,1),2)

 

[binar]

Ooops!

16 should be 160

=MOD(CEILING(ROWS(A$2:A2)/160,1),2)

T. Valko,
Huge thanks for your help ! (Again) I tried out your formula and found it interesting you are using the CEILING function. Never knew such a function existed. Nevertheless, I tried out your formula as a CONDITIONAL FORMATTING RULE and it works with an interesting flaw I can't fix.

ROW 1 is the HEADER row. And the first group of 160 rows count at 159 rows because the HEADER row is being included in the first group of 160 rows. However the second, third, fourth, fifth, etc... group of 160 rows count exactly at 160 rows each.

Is it possible to tweak your equation so that the Header Row1 is not included in the first group of 160 rows? Any help will be greatly appreciated.

Thanks again.

 

[Norie]

Try this slight modification to Biff's formula:

=MOD(CEILING((ROWS(A$2:A2)-1)/160,1),2)

 

[binar]

Try this slight modification to Biff's formula:

=MOD(CEILING((ROWS(A$2:A2)-1)/160,1),2)

Norie,
I tested your formula and it works nicely. The HEADER row is White followed by a group of 160 YELLOW Rows. The count between yellow and white rows is perfect at a 160 rows each. I just wish Microsoft would make such custom row highlighting more easy to setup. This task is very difficult without the help of talented Excel users like you and T. Valko.

Again thanks to all.

 

[T. Valko]

T. Valko,
Huge thanks for your help ! (Again) I tried out your formula and found it interesting you are using the CEILING function. Never knew such a function existed. Nevertheless, I tried out your formula as a CONDITIONAL FORMATTING RULE and it works with an interesting flaw I can't fix.

ROW 1 is the HEADER row. And the first group of 160 rows count at 159 rows because the HEADER row is being included in the first group of 160 rows. However the second, third, fourth, fifth, etc... group of 160 rows count exactly at 160 rows each.

Is it possible to tweak your equation so that the Header Row1 is not included in the first group of 160 rows? Any help will be greatly appreciated.

Thanks again.

I see you already have a solution...

Since the header row is a separate condition I assumed you would apply cf to that row as a separate condition and you needed help with the groups of 160 rows.

You would apply the formula I suggested starting on row 2.

Thanks for the feedback!