Color every second row ?

[SpookiePower]

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 ??

 

[Gary Keramidas]

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

 

[broro183]

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...

 

[Bob Phillips]

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]

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 A16 but could use A1100 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 A1100, starting from A1.

2) Go to Format>Conditional 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 A1100 will have the alternate row color/banding.

 

[SpookiePower]

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

 

[broro183]

Hi, try again...

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...

 

[Bob Phillips]

Maybe you need a formula of

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[SpookiePower]

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.

 

[SpookiePower]

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

 

[impslayer]

SpookiePower skrev:

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

Using "," or ";" is dependent on localization IIRC, thus an 'english
Excel install' uses "," but a 'swedish Excel install' uses ";". Erm, or
vice versa

/impslayer, aka Birger Johansson

 

[Bob Phillips]

Because we are using English language setting, where comma is not used as a
decimal separator in numbers, so it can be used as the function separator.
As Continental settings us a comma in numbers, a different character is used
as a function separator. If you noticed in my Danish version I included a ;.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[avveerkar]

I have written a small macro using CurrentRegion where in VBA detects
last column and last row in used area and only shades that range. I
also color header row differently. I strongly recommend that you store
this procedure or your own procedure in VBA project module and create a
custom button in a toolbar associated with this procedure. All that you
have to do is select any cell in the table and click this button it.
The procedure I wrote and use is ( I call it EasyRead named after
continuous stationery used for computer printouts )

Sub EasyRead()
Dim MyArea As Object
Set MyArea = ActiveCell.CurrentRegion
ColNum = MyArea.Columns.Count
RowNum = MyArea.Rows.Count
FirstCol = MyArea.Column
FirstRow = MyArea.Row

OddFlag = True
With Range(Cells(FirstRow, FirstCol), Cells(FirstRow, FirstCol +
ColNum - 1))
..Interior.ColorIndex = 40
..Font.Bold = True
End With
For i = 1 To RowNum - 1
Set NewRow = Range(Cells(FirstRow + i, FirstCol), Cells(FirstRow +
i, FirstCol + ColNum - 1))
If OddFlag = True Then
NewRow.Interior.ColorIndex = 2
Else
NewRow.Interior.ColorIndex = 20
End If
OddFlag = Not (OddFlag)
Next i
With MyArea.Borders
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With

End Sub