I would like to colour every second row. Like if I have the 100 first

rows in use then every second row should have a colour, and if I later

on add or delete more rows, then every second row should still be coloured.

I have manual coloured every second row, but if I delete a row then there

will be three rows with the same colour beside each other. Is it possible to do it this way ??


--
Mit fotoalbum
www.photo.activewebsite.dk

use conditional formatting

1st condition
=MOD(ROW(),2)=1

then choose your formats

2nd condition
=MOD(ROW(),2)=0
then choose your formats


i use both because i usually put boders around the cells and if you just use
1, the borders are around cells in every other row

use 1 of the above if you just want to color the rows

if you want code, this makes every other row in the specified range green
with borders around the cells


sub color_rows()
dim lastrow as long
lastRow = Worksheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row
With Range("C4:C" & lastRow)
.Interior.ColorIndex = 22
.Borders(xlLeft).LineStyle = xlContinuous
.Borders(xlRight).LineStyle = xlContinuous
.Borders(xlTop).LineStyle = xlContinuous
.Borders(xlBottom).LineStyle = xlContinuous
End With
end sub
--


Gary


"SpookiePower" wrote in message
. dk...
I would like to colour every second row. Like if I have the 100 first

rows in use then every second row should have a colour, and if I later

on add or delete more rows, then every second row should still be
coloured.

I have manual coloured every second row, but if I delete a row then there

will be three rows with the same colour beside each other. Is it possible
to do it this way ??


--
Mit fotoalbum
www.photo.activewebsite.dk

Hi,
Check out this link
http://www.ozgrid.com/Excel/alternate-row-color.htm.

Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=503235

"broro183" skrev i en meddelelse
...

Hi,
Check out this link
http://www.ozgrid.com/Excel/alternate-row-color.htm.

Thanks. But I can't get it to work.
I did what is mention below, but all the rows are still white ??
Automatically Expand/Contract Alternate Row Colors/Color Banding

The simple method shown above is fine for a static table, but it will apply the format to all odd row numbers that do not yet have
data. For example, we use the range A1:D6 but could use A1:D100 so that as our table has more data added the new row of data will be
color coded automatically, while all unused rows will remain blank.

1) Select the A1:D100, starting from A1.

2) Go to FormatConditional Formatting and choose "Formula is:"

3) In the box to the right, type the formula as shown below;

=AND(MOD(ROW(),2),COUNTA($A1:$D1))

4) Click the "Format" button and choose your desired cell shading under "Patterns". Then "Ok" and "Ok" again.

Now only the used range of A1:D100 will have the alternate row color/banding.

See http://www.xldynamic.com/source/xld.CF.html#rows for an example.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SpookiePower" wrote in message
. dk...
I would like to colour every second row. Like if I have the 100 first

rows in use then every second row should have a colour, and if I later

on add or delete more rows, then every second row should still be
coloured.

I have manual coloured every second row, but if I delete a row then there

will be three rows with the same colour beside each other. Is it possible
to do it this way ??


--
Mit fotoalbum
www.photo.activewebsite.dk

I cant make the most simple one of them to work.
Either all the rows are white or I get this error message -
http://www.activewebsite.dk/excel.jpg
and then it point at ,2 in the line =MOD(ROW(),2)=0

I'm using excel 2003

Maybe you need a formula of

=REST(RÆKKE();2)=0

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SpookiePower" wrote in message
. dk...
I cant make the most simple one of them to work.
Either all the rows are white or I get this error message -
http://www.activewebsite.dk/excel.jpg
and then it point at ,2 in the line =MOD(ROW(),2)=0

I'm using excel 2003

"Bob Phillips" skrev i en meddelelse ...
Maybe you need a formula of

=REST(RÆKKE();2)=0

I'm using the english version not the danish. But I'll try it.

I found the error.

In all the examples I have found they use a "," before 2 -
=MOD(ROW(),2)

It works when I use ";" insted -
=MOD(ROW();2)

But why do the examples use ","

http://www.xldynamic.com/source/xld.CF.html#top
http://www.ozgrid.com/Excel/alternate-row-color.htm

Hi, try again...
SpookiePower Wrote:
I cant make the most simple one of them to work.
Either all the rows are white or I get this error message -
http://www.activewebsite.dk/excel.jpg
and then it point at ,2 in the line =MOD(ROW(),2)=0
I'm using excel 2003

Have another look at the example, all that needs to be entered is
"=MOD(ROW(),2)", your picture shows that you have typed
"=MOD(ROW(),2)=0".

The "=0" is not needed.

hth
Rob Brockett
NZ
always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=503235