Format every "n" rows

[Ed]

Hello everyone,
I have a question. I am currently working with a very long List of items. To
make the list more reading-friendly, I would like to have a horizontal border
line every 5 rows, or every 5th row to have that row with a different color
fill for example.

On my first column I have the number of item. One way in which I thought it
could work was to use the ISEVEN combined with RIGHT to have Excel check the
last number of the item number and apply a Conditional Formatting if cell
value is 0 or 5. I don't know what is going on but for some strange reason it
doesn't even recognize the simple ISEVEN function...

Anyways the problem I was going to have later on is that when I would apply
a Filter to the List the those horizontal lines would be all mixed up. Is
there any way to apply something like a Format but to the Background? Not as
a image but more like something that no matter what every "n" rows I will
have a border line?

,thanks
Ed

 

[Gazeta]

Hello everyone,
I have a question. I am currently working with a very long List of items. To
make the list more reading-friendly, I would like to have a horizontal border
line every 5 rows, or every 5th row to have that row with a different color
fill for example.

On my first column I have the number of item. One way in which I thought it
could work was to use the ISEVEN combined with RIGHT to have Excel check the
last number of the item number and apply a Conditional Formatting if cell
value is 0 or 5. I don't know what is going on but for some strange reason it
doesn't even recognize the simple ISEVEN function...

Anyways the problem I was going to have later on is that when I would apply
a Filter to the List the those horizontal lines would be all mixed up. Is
there any way to apply something like a Format but to the Background? Not as
a image but more like something that no matter what every "n" rows I will
have a border line?

,thanks
Ed

check conditional formatting and combine it with number of row
mcg

 

[Therese]

Hi
Beforhand you can insert an "n" colomn (Column A) which you can hide when
done. Write an n in the cells next to the rows that you want to format.
Click in B1. Format<conditional formatting< the formular is =$A1="n".
Then click the formatting brush, while still being in B1. Then click at the
top of the columns that you want to format.

Hope it works.

 

[Gord Dibben]

Select all cells.

Format>Conditional Formatting>Formula is: =MOD(ROW(),5)=1

Pick a pattern from Format and OK your way out.

BTW.............ISEVEN and ISODD are functions contained in the Analysis ToolPak
and must be loaded through Tools>Add-ins.


Gord Dibben MS Excel MVP

 

[Ed]

Thank you everyone, the information has been useful!

,Ed