How can I color every other row

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.
0
MrsMrfy (10)
2/13/2008 3:26:58 PM
excel 39879 articles. 2 followers. Follow

6 Replies
417 Views

Similar Articles

[PageSpeed] 48

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
0
petersod (12004)
2/13/2008 4:16:22 PM
Select your range.
Format > AutoFormat > List1
Options > Formats to apply >
Uncheck all except Pattern
0
2/13/2008 5:18:58 PM
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" <MrsMrfy@gmail.com> wrote in message 
news:25f380df-2104-45d5-b346-3cb606b43c31@e6g2000prf.googlegroups.com...
> 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. 


0
bliengme5824 (3040)
2/13/2008 5:36:18 PM
On Feb 13, 10:16=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> On way:
>
> =A0 =A0 Dim iCtr As Long =A0 =A0
> =A0 =A0 With ActiveSheet.UsedRange
> =A0 =A0 =A0 =A0 For iCtr =3D .Row To .Rows(.Rows.Count).Row Step 2
> =A0 =A0 =A0 =A0 =A0 =A0 With .Rows(iCtr).Interior
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .ColorIndex =3D 36
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Pattern =3D xlSolid
> =A0 =A0 =A0 =A0 =A0 =A0 End With
> =A0 =A0 =A0 =A0 Next iCtr
> =A0 =A0 End With
>
> You may want to look at conditional formatting, too:http://www.cpearson.co=
m/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. =A0Filling 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:
> > =A0 =A0 =A0 =A0 =A0 =A0TheRange.Activate
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0With Selection.Interior
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.ColorIndex =3D 36
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0.Pattern =3D xlSolid
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0End 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.
0
2/13/2008 7:46:04 PM
On Feb 13, 11:36=A0am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> Or use Format Conditional formatting:
> Formula IS; =3DMOD(ROW(),2)=3D0 ; set colour
> Formula IS: =3DMOD(ROW(),2)=3D1; 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-3cb606b43c31@e6g2000prf.googlegroups.com...
>
>
>
> > Help please.
>
> > I want to make it easier to use a large spreadsheet where two rows are
> > used for each record. =A0Filling 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:
> > =A0 =A0 =A0 =A0 =A0 TheRange.Activate
> > =A0 =A0 =A0 =A0 =A0 =A0 With Selection.Interior
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .ColorIndex =3D 36
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Pattern =3D xlSolid
> > =A0 =A0 =A0 =A0 =A0 =A0 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.
0
2/13/2008 7:49:48 PM
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" <MrsMrfy@gmail.com> wrote in message 
news:25f380df-2104-45d5-b346-3cb606b43c31@e6g2000prf.googlegroups.com...
> 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. 


0
Tyro (331)
2/14/2008 12:05:47 AM
Reply:

Similar Artilces:

Can't Send/Recieve
For some odd reason thins morning when I start Windows Mail it claims there are 'No new Messages.WM is set to 'Send and Recieve at Startup.' Also the Send/Recieve button is disabled. When I go online and access my email account with my it shows several emails sated today. I was using Windows Live Mail and had problems uninstalling it and reluctantly went and deleted the folder for Windows Live 'casue I don't use any of the other products. Any ideas, Thanks, James Somehow the account got removed. I recreated it and it's ok now. Thanks, Jame...

Change background color of query results from access in worksheet
Hi, I have Excel 2007 and selected Date => From Access and then navigated to an Access DB and selected a query. I chose "Table" view. The query results now appear in my worksheet which is great. However, I want to change the background. Right now it alternatives by row between a light blue and a darker blue. I want the results to have a white background with black borders but have been unable to change the format. If I go to Home and select No fill for a color background nothing happens. Is there a way to change the query results? Thanks, Could be the r...

How can I drop the year from mm/dd/yyyy data?
I have a large data set consisting of values from multiple years that I would like to analyze by time of year. ie: I want to look at the month/day for each value while ignoring the year. Specifically I want to end up with a graph showing time of year (in months) across the x-axis and showing all my data points as if they were from this one hypothetical year. Seems like it should be simple but I can't seem to crack it. Any help is greatly appreciated. Hi, Try Format > Cells > Custom: dd/mm -Mark >-----Original Message----- >I have a large data set consisting of valu...

How can I convert text to all lowercase?
How can I convert text to all lowercase? I know that I can use the Format menu to convert a block of text to all uppercase, but is there any way to convert it to lowercase? If not with Excel, how about Word, DOS, any other way. I'm using Excel 7.0 (Office 95). Thanks in advance! Take a look at David McRitchie's Lower() Macro (I believe it will work in XL7): http://www.mvps.org/dmcritchie/excel/proper.htm#lower In article <20031207155812.23043.00000330@mb-m27.aol.com>, hbyardsale@aol.comnetorg (HBYardSale) wrote: > How can I convert text to all lowercase? > &g...

Invoice deleted in Batch, can't re-invoice.
We shipped one line of a multi line sales order. We accidentally deleted the batch that contained the invoice. The line item will not let us fulfill again, or re-invoice. To get the invoice we re-added another line item. Now we need to delete the original line item, but it won't let us delete, it says "Item has previous qtys and cannot be deleted." When we print the packing list the messed up line item shows. Can we get rid if that line item somehow? -- Sheri Salomone THANKS! When you transfer a Sales Order to an Invoice, it updates all the quantities for you. If you adde...

Can not reset admin password under safe mode due to password policy?
Hi, all. I'll have to log into the default administrator account, because I can not do anything under the current standard user account status. I went to the safe mode, and tried to log into the admin account, but the password is forgotten. I built the reset password USB disk under the account before. Now I follow the instructions for resetting the password. However, the window popped up, and it said "ineffective password, the password length is too short, or ca not conform to the password policy. I've tried passwords of different lengths, such as 3,6,8,16, 20. All tempt...

Why Can't I Access Internet Links
I use Windows XP and IE 7 and suddenly one day I was unable to access internet links that were sent to me thru regular emails to my Microsoft Outlook. Any idea how to fix it? Thanks. What happens when you try? Any error messages? Hal -- Hal Hostetler, CPBE -- hhh@kvoa.com Senior Engineer/MIS -- MS MVP-Print/Imaging -- WA7BGX http://www.kvoa.com -- "When News breaks, we fix it!" KVOA Television, Tucson, AZ. NBC Channel 4 Live at Hot Licks - www.badnewsbluesband.com "amirstal" <amirstal@gmail.com> wrote in message news:d7decb35-157c-4438-82bd-24a2536d347f@m36g2...

in CRM Mobile, Can we assign / apply rules ?
Both in CRM Mobile and Mobile Express, I didn't see any function about assign / apply rules / convert ... These functions are not available out of box. You can however, create workflow rules that "watches" for some changes to field records so it can be triggered wheneven those fields are updated by CRM Mobile or Mobile Express. Frank Lee, Microsoft CRM MVP Workopia, Inc. http://microsoft-crm.spaces.live.com "Goldensun" wrote: > Both in CRM Mobile and Mobile Express, I didn't see any function about assign > / apply rules / convert ... ...

can't access public folders with OWA
Hi, I have Windows Server 2003 and Exchange Server 2003 Installed everything works fine, except when trying to access Public folders via Outlook Web Access, after going to the location its asks for username and password - this works ok and the folder is displayed, but when trying to add a folder or post a note or do anything I get: You are not authorized to view this page You might not have permission to view this directory or page using the credentials you supplied. I have tested on client machines, remote machine and the exchange server as administrator with no luck. does anyone have any...

Can Excel generate a 3-D scatter plot?
I want to generate surface plots of the form (x,y,f(x,y)). Should I just go buy MathCAD, or can Excel manage this? Hi Andy Pope has 'tricked' one: http://andypope.info/charts/3drotate.htm But you probably go for MathCad :-) -- Regards Frank Kabel Frankfurt, Germany "zzzfizz" <zzzfizz@discussions.microsoft.com> schrieb im Newsbeitrag news:BA2BEAB6-63E1-46E7-816F-B282C590C3D9@microsoft.com... > I want to generate surface plots of the form (x,y,f(x,y)). Should I just go > buy MathCAD, or can Excel manage this? ...

Can an email be read without internet connection?
Am downloading an email to CD, but the computer I need the CD for has no internet connection at present. Can I read & print the email without access to internet? This is an HP notebook & the computer I need to read & print the CD in is an IBM Aptiva 247. Hula Girl wrote: > Am downloading an email to CD, but the computer I need the CD for has > no internet connection at present. Can I read & print the email > without access to internet? This is an HP notebook & the computer I > need to read & print the CD in is an IBM Aptiva 247. Not sure what you mean b...

How can you set up a form that the cursor only moves to cells tha.
I have set up a form. Now I want my cusor to move through the worksheet only in certain cells. I want to be able to go from one cell that needs to be filled out to the next cell that needs to be filled out automatically. Example , move from cell a3 to c19 automatically. How can I do that? Hi! You can accomplish that by setting sheet protection. Select all the cells that you want to be able to navigate to then goto Format>Cells>Protection tab. Uncheck Locked. Now, goto Tools>Protection>Protect sheet. A list of options will appear. Uncheck Select locked cells and check...

How can I autofill dates having a blank cell between each day?
Hi Dee, Enter a date in cell A1 (eg 5/13/08), enter the next day's date in cell A3 (eg 5/14/08). Select cells A1 trough to A4 (ie select two dates and two blank cells). See the little dot at the bottom right of the selection rectangle? Click on that (the cursor will change to a cross when you are over the right spot) and drag it down as far as you want. Ed Ferrero www.edferrero.com ...

Can't set Date Properties.
I am working with data that has dates set as text. So in a query I am using a variable that sets the text to a date suing CDATE. At least I thought it did. I notice that in forms etc when I try to set the date property to long, medium etc, there is nothing to choose so presumably it is not a date after all! Any idea what I'm doing wrong please. Cheers. Roger --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.673 / Virus Database: 435 - Release Date: 01/05/2004 Sorted. I hadn't set the function as a date. Well I am n...

can't convert jpg or other into publisher
I reinstalled publisher after reformatting my xp Dell. Now publisher will not convert image into the page where I want a picture. I can scan one in, but can't import clip art or a picture located elsewhere. I can import a picture into word. I reinstalled publisher again and same thing. I put it on a friends computer and everything works fine - I'm puzzled. What are they referring to when it's saying can't convert the image - also mentions a graphic filter?? I need help. Jeanne Importing from where? Are you using the Clip Gallery or Organizer? If you are trying to import ...

Color in Bar Graph
I have a Report with a Bar Graph on it, I would like to make each Category (up to 4 on a chart), a specific color. Thanks..ernst ...

rows....
i have a worksheet of vendor account details. in one column there are vendor numbers & some vendors have 5 line items some have10,15,50,etc i.e. line items are different. Now the problem is every month i have to prepare vendors template and i have to insert 5 rows after every change in vendor number in vendor column. right now i am going manually at each vendor change and inserting rows as detail above, there are about 700 vendors. this is not only time consuming but frustating job. so there any way to develop macro for this or any other way so that by giving one command the above...

Can the visible gridlines on a sheet be removed?
Is there a way to remove all or just a section of the grid on a spread sheet? Not for printing, I know how to do that. I have several charts on a sheet but I want the grid lines on the sheet to be non-visible so it looks like a blank sheet. Toby Erkson Oregon, USA highlight the area>format>patterns>fill color white -- Don Guillett SalesAid Software donaldb@281.com <Toby Erkson> wrote in message news:cvv490lu6ohp1d56i71k555ke3m092jf2s@4ax.com... > Is there a way to remove all or just a section of the grid on a spread sheet? Not for printing, I know how to do that. ...

static control color in Custom Control in MFC
Hi, In the win32 application I have written WM_CTLCOLORSTATIC which was processed and white brush was returned(GetStockObject(WHITE_BRUSH)), so the static control which is child windows of the custom control looked white. In the corresponding MFC program I have the OnCtlColor for the "custom control" handled as:- HBRUSH CEditHold::OnCtlColor(CDC* pDC, CWnd* pWnd, UINT nCtlColor) { HBRUSH hbr = CWnd::OnCtlColor(pDC, pWnd, nCtlColor); int id = pWnd->GetDlgCtrlID(); if(nCtlColor == CTLCOLOR_STATIC && (id > 0 && id < 9)) { return (HBRUSH)GetStockObje...

can send but not reply mail in outlook 2003
Hi there, I have a strange problem: I can send but not reply to external mail in outlook 2003. Anyone have the answer? Thanks Malcolm "ms" <keppeltt@d1.com.sg> wrote in news:#tT#qAv9EHA.2568 @TK2MSFTNGP11.phx.gbl: > Hi there, > I have a strange problem: I can send but not reply to external mail in > outlook 2003. Anyone have the answer? > > Thanks > Malcolm > > > What happens when you try? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Stockholm Consulting Group/KSG http://www.scgab.com Microsoft OneNote FAQ: http://home.hawaii.rr.com/schorr...

Can I config Outlook folders in my archive.pst to hold both Post and Notes?
This is a multi-part message in MIME format. ------=_NextPart_000_0010_01C7F9DE.84A377F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Can I config Outlook folders in my archive.pst to hold both Post and = Notes? If so how? TIA Chris. ------=_NextPart_000_0010_01C7F9DE.84A377F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"t...

Can formulas in cells be made to remain if the data is deleted?
Can you explain in a bit more detail what your question/problem is? -- Kind regards, Niek Otten "wendyp" <wendyp@discussions.microsoft.com> wrote in message news:B7A92B0A-E752-4D58-90FF-3DE5F292D35C@microsoft.com... > Hi Wendy, See Insert a Row using a Macro to maintain formulas http://www.mvps.org/dmcritchie/excel/insrtrow.htm To simply remove constants from a selection within a macro Selection. SpecialCells(xlConstants).ClearContents To remove constants manually from a selection Edit, GoTo (Ctrl+G), Constans [you can pick what kind ...

Can't log into admin profile
I was prompted to install an update to my Win7 machine. After restarting, I find that my password won't work and I cannot log on to the admin profile of my computer. What do I need to either fix this problem or return to my previous configuration ? ...

Looking for help...I can't send e-mail using CRM web client
I'm having a problem with trying to send e-mails vie the CRM web client. When I try to send the e-mail I get a pop-up with the message in bold "Unexpected Error" "An error has occurred. For more information, contact your system administrator". I've checked the event logs, no errors are showing up in either my CRM server or my exchange server. I'm lost, new to CRM. Any help or ideas is appreciated. Thanks, You could try to add pre-windows2000 account to the administrator group on the exchange server. Some time that helps "Marty" wrote:...

stacked fields 'can grow' but overlap in report
I have four fields stacked one above the other on a report. They're all marked as 'Can Grow', and sometimes they do. In one case, a multi-line data field pushes the field below out of the way. That's good. But in another case, a multi-line field overlaps with the field below. In every case the fields are standard text boxes. However, I noticed there is a difference in the associated label. The labels that push down with their fields do not have any events available for attaching code. The other labels have events, although I'm not using any events. ...