How can I color every other row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MrsMrfy - 13 Feb 2008 15:26 GMT

Help please.

I want to make it easier to use a large spreadsheet where two rows are
used for each record. Filling in the background color of every second
row prevents mistakes when entering data. I want to color only the
used range, not the entire row.

I recorded a macro and got the following:
TheRange.Activate
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

I really appreciate the help.

Reply to this Message

Dave Peterson - 13 Feb 2008 16:16 GMT

On way:

Dim iCtr As Long
With ActiveSheet.UsedRange
For iCtr = .Row To .Rows(.Rows.Count).Row Step 2
With .Rows(iCtr).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Next iCtr
End With

You may want to look at conditional formatting, too:
http://www.cpearson.com/excel/banding.htm

Or even Format|Autoformat (in xl2003 menus)

> Help please.
>
[quoted text clipped - 11 lines]
>
> I really appreciate the help.

Signature

Dave Peterson

Reply to this Message

Lolly - 13 Feb 2008 19:46 GMT

> On way:
>
[quoted text clipped - 33 lines]
>
> - Show quoted text -

Dave you are wonderful. I tried your code and it worked perfectly and
was exactly what I wanted. Thanks a million.

Reply to this Message

Herbert Seidenberg - 13 Feb 2008 17:18 GMT

Select your range.
Format > AutoFormat > List1
Options > Formats to apply >
Uncheck all except Pattern

Reply to this Message

Bernard Liengme - 13 Feb 2008 17:36 GMT

Or use Format Conditional formatting:
Formula IS; =MOD(ROW(),2)=0 ; set colour
Formula IS: =MOD(ROW(),2)=1; set another colour
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

> Help please.
>
[quoted text clipped - 11 lines]
>
> I really appreciate the help.

Reply to this Message

Lolly - 13 Feb 2008 19:49 GMT

On Feb 13, 11:36 am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:

> Or use Format Conditional formatting:
> Formula IS; =MOD(ROW(),2)=0 ; set colour
[quoted text clipped - 22 lines]
>
> - Show quoted text -

I used Dave's code because it seemed to fit my needs exactly but I
appreciate the solutions offered by others. I will keep your
suggestions as well. They may serve better at another time. Thanks.

Reply to this Message

Tyro - 14 Feb 2008 00:05 GMT

You don't state what version of Excel you're using; in Excel 2007, you can
make a table of your data and have Excel band every other row.

Tyro

> Help please.
>
[quoted text clipped - 11 lines]
>
> I really appreciate the help.

Reply to this Message