Hi,

I'm trying to write a macro that will set a different background color
for every other row of the selection (not of the whole worksheet).

I figured that I would just need to loop through each cell of the
selection, test whether it's in an even-number row, and if so, set its
background color. So if the selection covers 3 columns, every other set
of 3 cells in the selection would get a background color.

But... there doesn't seem to be a property to test the row number of a
given cell! So how could I work this out?

Thanks in advance for your help.

Raph

this should work fine:

Sub test()

For Each row_ In Selection.Rows
If row_.Row() Mod 2 = 1 Then
row_.Interior.ColorIndex = 35
End If
Next row_

End Sub

you might want to change the if-statement,
now it always colors the uneven rows, if
you want to start coloring the second row of
your selection you should change it!

hth

Carlo


" wrote:

Hi,

I'm trying to write a macro that will set a different background color
for every other row of the selection (not of the whole worksheet).

I figured that I would just need to loop through each cell of the
selection, test whether it's in an even-number row, and if so, set its
background color. So if the selection covers 3 columns, every other set
of 3 cells in the selection would get a background color.

But... there doesn't seem to be a property to test the row number of a
given cell! So how could I work this out?

Thanks in advance for your help.

Raph

You can use Conditional formatting for the required selection with this
formula:
=MOD(ROW(),2)=0

Regards,
Stefi


€� ezt Ã*rta:

Hi,

I'm trying to write a macro that will set a different background color
for every other row of the selection (not of the whole worksheet).

I figured that I would just need to loop through each cell of the
selection, test whether it's in an even-number row, and if so, set its
background color. So if the selection covers 3 columns, every other set
of 3 cells in the selection would get a background color.

But... there doesn't seem to be a property to test the row number of a
given cell! So how could I work this out?

Thanks in advance for your help.

Raph

No need for a macro, use conditional formatting, see

http://www.xldynamic.com/source/xld.CF.htmlrows

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Hi,

I'm trying to write a macro that will set a different background color
for every other row of the selection (not of the whole worksheet).

I figured that I would just need to loop through each cell of the
selection, test whether it's in an even-number row, and if so, set its
background color. So if the selection covers 3 columns, every other set
of 3 cells in the selection would get a background color.

But... there doesn't seem to be a property to test the row number of a
given cell! So how could I work this out?

Thanks in advance for your help.

Raph

Carlo wrote::

For Each row_ In Selection.Rows

Aargh, of course! I didn't even think that a Selection has a Rows
collection!

Thank you very much!

Raph

Looks like the # got stripped from Bob's link.

http://www.xldynamic.com/source/xld.CF.html#rows

Zilbandy wrote:
On Fri, 15 Sep 2006 09:08:18 +0100, "Bob Phillips"
wrote:


No need for a macro, use conditional formatting, see

http://www.xldynamic.com/source/xld.CF.htmlrows


Link doesn't work for me. :(


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Stefi wrote:

You can use Conditional formatting for the required selection with this
formula:
=MOD(ROW(),2)=0

Conditional formatting is defined according to the cell's value, not to
a testing formula... Where should I input the formula?

Raph

a écrit :

Stefi wrote:

You can use Conditional formatting for the required selection with this
formula:
=MOD(ROW(),2)=0

Conditional formatting is defined according to the cell's value, not to
a testing formula... Where should I input the formula?

Forget it! I found it... Actually, it's even better because the
striping will remain even if inserting new rows!

Thanks!

Raph