How can I color every other row

[MrsMrfy]

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.

 

[Dave Peterson]

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)

 

[Herbert Seidenberg]

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

 

[Bernard Liengme]

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

 

[Lolly]

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)









--

Dave Peterson- Hide quoted text -

- Show quoted text -

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

 

[Lolly]

Or use Format Conditional formatting:
Formula IS; =MOD(ROW(),2)=0 ; set colour
Formula IS: =MOD(ROW(),2)=1; set another colour
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email









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

 

[Tyro]

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