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.

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)

MrsMrfy wrote:
>
> 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

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

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 MVP
http://people.stfx.ca/bliengme
remove caps from email

"MrsMrfy" <(E-Mail Removed)> wrote in message
news:25f380df-2104-45d5-b346-(E-Mail Removed)...
> 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.

On Feb 13, 10:16*am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> 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)
>
>
>
>
>
> MrsMrfy wrote:
>
> > 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- 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.

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
> 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
>
> "MrsMrfy" <MrsM...@gmail.com> wrote in message
>
> news:25f380df-2104-45d5-b346-(E-Mail Removed)...
>
>
>
> > 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.- Hide quoted text -
>
> - 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.

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

"MrsMrfy" <(E-Mail Removed)> wrote in message
news:25f380df-2104-45d5-b346-(E-Mail Removed)...
> 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.