Background color for every other row

[rgoubet]

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

 

[Guest]

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

 

[Guest]

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

Regards,
Stefi


„[email protected]†ezt írta:

 

[Bob Phillips]

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)

 

[Zilbandy]

No need for a macro, use conditional formatting, see

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

Link doesn't work for me.

 

[NickHK]

Remove the "rows" from the end of the URL.

NickHK

 

[rgoubet]

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

 

[Andy Pope]

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

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

 

[Bob Phillips]

Sorry, mis-transcribed

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

--
HTH

Bob Phillips

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

 

[rgoubet]

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

 

[rgoubet]

(e-mail address removed) a écrit :

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