How do I set an auto alternating row color?

I'd like to set my excel database ROWS to alternating colors(ex. teal & 
white). My database is HUGE and I can't feasibly do it manually without 
taking hours and hours clicking each row and changing the color. Also another 
concern is that I constantly am adding rows throughout the database. I'd like 
to know if there's a way to automatically have to row colors stay alternating.

Thanks a ton!

Daniel
0
7/19/2005 3:46:08 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
316 Views

Similar Articles

[PageSpeed] 5

Daniel,

Select all your cell, the use Format / Conditional Formatting...  Formula is... and use the formula

=MOD(ROW(),2)=0
(for the first row to not be shaded)
or
=MOD(ROW(),2)=1
(for the first row to be shaded)

and set your color as you want by clicking the Format button, and selecting your fill color on the 
patterns tab.  Then click OK OK and you're done.

HTH,
Bernie
MS Excel MVP


"Daniel Stephens" <Daniel Stephens@discussions.microsoft.com> wrote in message 
news:DB4A585C-61D0-4BD9-ABFE-C91C7076AD92@microsoft.com...
> I'd like to set my excel database ROWS to alternating colors(ex. teal &
> white). My database is HUGE and I can't feasibly do it manually without
> taking hours and hours clicking each row and changing the color. Also another
> concern is that I constantly am adding rows throughout the database. I'd like
> to know if there's a way to automatically have to row colors stay alternating.
>
> Thanks a ton!
>
> Daniel 


0
Bernie
7/19/2005 4:09:32 PM
Thank you SO much. That was very very helpful.

"Bernie Deitrick" wrote:

> Daniel,
> 
> Select all your cell, the use Format / Conditional Formatting...  Formula is... and use the formula
> 
> =MOD(ROW(),2)=0
> (for the first row to not be shaded)
> or
> =MOD(ROW(),2)=1
> (for the first row to be shaded)
> 
> and set your color as you want by clicking the Format button, and selecting your fill color on the 
> patterns tab.  Then click OK OK and you're done.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Daniel Stephens" <Daniel Stephens@discussions.microsoft.com> wrote in message 
> news:DB4A585C-61D0-4BD9-ABFE-C91C7076AD92@microsoft.com...
> > I'd like to set my excel database ROWS to alternating colors(ex. teal &
> > white). My database is HUGE and I can't feasibly do it manually without
> > taking hours and hours clicking each row and changing the color. Also another
> > concern is that I constantly am adding rows throughout the database. I'd like
> > to know if there's a way to automatically have to row colors stay alternating.
> >
> > Thanks a ton!
> >
> > Daniel 
> 
> 
> 
0
7/19/2005 4:22:03 PM
Reply:

Similar Artilces:

How to delete or permantly change logo in personal settings
When I was just learnng how to do publisher I created a logo and Iwant to change it in the permanant settings for the personal information. I cannot delete it or edit it. Also on the other 3 settings for secondary, home etc. it does not alowany logo at all. Anyone know? Locate the following files and delete them: Primary Business - biz1logo.jsp Secondary Business - biz2logo.jsp Other Organization - Orglogo.jsp Home/Family - Perslogo.jsp -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no ...

Highlighting the active row with special colour
Hi there Is there a macro that highlights the whole active row (not only the active cell) that the cursor is in, in an Excel worksheet? Thanks Raj Highlight it in a particular colour, I meant... that would move along when I would move the cursor onto another row.... "Raj Mazumdar" wrote: > Hi there > > Is there a macro that highlights the whole active row (not only the active > cell) that the cursor is in, in an Excel worksheet? > > Thanks > > Raj Try this '---------------------------------------------------------------- Private Sub Workshe...

Color Picker in Excel
Hi All, I will appreciate if someone can guide me to do a color picker using VBA in excel. I need to display the 57 colors of the palette and get the color index returned to put that value in a cell. A simple box with colors to do this using userform on xl's inbuilt dialog will be fine. Looking forward to all your help ! Thanx and regds Shuvro '-----------------------------�------------------------------�-------------- -- Function GetColorindex(Optional Text As Boolean = False) As Long '-----------------------------�------------------------------�-------------- -- Dim rngCurr...

Templates in alternative startup location are not recognized
I've just moved up to Office 2003 from 2000. In Excel, all the templates that I have been using from our group templates location (set as the "At startup, open all the files in" location) are not recognized. Two or three (out of 14) are listed without icons, and when you click them, the pointer changes briefly and nothing happens. They are also shown when you click the "On my computer" link, but behave the same way (i.e. do nothing) when clicked. These are prper templates (Save as, *.xlt etc), and I've even recreated one using Excel 2003 with the same effec...

Outlook 2007 256 Color Mode
Just returned home from a business trip, opened up my Outlook 2007 and it was UGLY! Looks like its running on 256 color mode or something. Running on Microsoft Vista / Office 2007 / Dell GX620 pc. It was working before I left. Not sure what cause or solution is. Tried doing a Office Repair...no affect. HELP! If you have a solution, please e-mail me at mcsims@gmail.com as I may loose this site location. Thanks...Michael Is it ONLY Outlook that looks that way? -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com <mcsims@gmail.com> wrote in message ...

Change cell background color based on content that results from li
This could be very simple, but I will lay the groundwork first. I have a schedule spreadsheet that I import data to from a web based program. There are existing filters to remove all formatting of the data and remove that which we do not use. What remains is a non formatted sheet that other workbooks link to so we can produce daily sheets. Now the data that is linked on the other sheets may be for example the number 150. It appears throughout the sheet and I would like to color any cell that contains the number 150. The problem is I can't search for 150 because it reall...

Auto Calculate Monthly Time Pace
I need to show how far into a month we are at any given time in % form. I want this to change every day automatically. Example: Today is day 27 of 31 days in January - 87% if today was 2/17/10 it would need to show 17/28 or 61%, 12/20/10 would be 20/31 or 65%, and so on for each day throughout 2010 (12/20/10 would be 65%) Is there a formula or function to do this? Thanks! Heather "HeatherJ" wrote: > I need to show how far into a month we are at any given time in % form. [....] > Today is day 27 of 31 days in January - 87% One way: =day(today()) / ...

more than 65,536 rows
Hi Is it possible to have more than 65,653 rows on a worksheet? The data I have does not fit on a normal sheet Harald Harald, Unfortunately, the maximum number of rows on a worksheet is 65,536 rows and cannot be increased. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Harald Bock" <anonymous@discussions.microsoft.com> wrote in message news:D6E9402F-92F4-4649-A72C-BC38BE6C89EB@microsoft.com... > Hi, > > Is it possible to have more than 65,653 rows on a worksheet? The data I have does not...

Rows and Columns Settings Problem
How do you set rows and columns in a way that when you scrol down/column you can always see a certain row(s)/column(s) -- Message posted from http://www.ExcelForum.com Check out XL Help for "Freeze Panes" In article <JMorgan.1ad5vf@excelforum-nospam.com>, JMorgan <<JMorgan.1ad5vf@excelforum-nospam.com>> wrote: > How do you set rows and columns in a way that when you scroll > down/column you can always see a certain row(s)/column(s). Thank -- Message posted from http://www.ExcelForum.com ...

Minimizing Rows of with similar data
I am using Excel 2007. I have 3 columns A, B, and C such as below: TU10-10 TU10 Tungsten Satin Wedding Band TU10-10.5 TU10 Tungsten Satin Wedding Band TU10-11 TU10 Tungsten Satin Wedding Band TU10-11.5 TU10 Tungsten Satin Wedding Band TU10-12 TU10 Tungsten Satin Wedding Band TU10-12.5 TU10 Tungsten Satin Wedding Band TU10-13 TU10 Tungsten Satin Wedding Band TU10-6 TU10 Tungsten Satin Wedding Band TU10-6.5 TU10 Tungsten Satin Wedding Band TU10-7 TU10 Tungsten Satin Wedding Band TU10-7.5 TU10 Tungsten Satin Wedding Band TU10-8 TU10 Tungsten Satin Wedding Band TU10-8.5 TU10 Tung...

Returning Data from a third cell in same row that meets two other
I'm looking for a formula that will return a result from a cell on a same row as two other cells that meet certain criteria. I'm sure there is a way to do this but i am a novice at this kind of stuff and can't seem to figure it out. For example i want a cell to = what is in column E when column A="36751" and when column B="Total Returns" The spreadsheet has 55000 rows. there will only be one instance where both these criteria are met. i want to use this to create a seperate spreadsheet with just info i need and can update on a daily basis. thanks in ...

How do I filter rows based upon a column value
I have a spreadsheet that contains multiple agency id's in a column. When generating reports, I would like to filter per agency and display only the rows associated with that agency. Is there a tutorial or sample on how to do this? Hi It sounds like you are looking for Data / Filter / AutoFilter. Have a look here for some basics: http://www.contextures.com/xlautofilter01.html -- Andy. "Jack" <nfr@nospam.com> wrote in message news:eqiU08TVEHA.2988@TK2MSFTNGP10.phx.gbl... > I have a spreadsheet that contains multiple agency id's in a column. When > generati...

List all row source for all forms, reports etc.
Hi I'm doing some work cleaning out old unused forms, reports and queries. I have been going through each report in a database (There are A LOT), determining its row source query, then marking it for deletion. I will eventually end up going through and deleting all the unused queries. As you can imagine this is time consuming, and I was thinkg 'there must be a FASTER way" Does anyone have a suggestion, or link to a pre-built function or model that could assist me? Regards Darragh On Thu, 17 Jan 2008 22:14:18 -0800 (PST), Darragh <darragh.murray@gmail.com> wrote: >Hi...

Alternating between sheets of same workbook
Hi Is there a keyboard way of switching between sheets of the same Excel workbook, like Ctrl+TAB switches between windows Hi Lourens Ctrl-PageUP and PageDown See also http://www.contextures.com/excel.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Lourens" <Lourens@discussions.microsoft.com> wrote in message news:CD7C213D-921A-4EE8-8517-096AD17316DB@microsoft.com... > Hi > Is there a keyboard way of switching between sheets of the same Excel > workbook, like Ctrl+TAB switches between windows Taken from Excel's Help with search word "Shortcu...

Restoring auto-archive properties
I have set up my archive folders to archive each sub-folder of my inbox separately. I have since found this too convoluted to search for items and i wish to restore the default settings so that, my inbox and its subfolders now appear as a subfolder of the Archive folders drop down. How can i do this without losing any of my previous data/e-mails? Simply change the settings and move over any item that you want to be somewhere else now. If this results in multiple empty pst-file, then you can disconnect those via; File-> Data File Management... -- Robert Sparnaaij [MV...

How to get Row # in formula?
I have several rows with the same formula but they are seperated, in some cases, by rows with other information to prevent me from just dragging a formula down to other rows. Ex. While in row 13: B13*C13+B13*D13+B13*E13 While in row 20: B20*C20+.... how can I write a formula to populate the "13" or "20" automatically for whatever row I am in? Thanks, Dave You may not be able to drag it, but if you copy row13 formula and go to row 20 it updates does it not? -- HTH Bob "DaveR" <DaveR@discussions.microsoft.com> wrote ...

count rows
Hi I have two worksheets - sheet1 and sheet2. Sheet1 is my main data which contains 7 colums (A-G) and number of rows varies depend on data. In sheet2 I have formula in Row1 (A1:G1) and I need to copy down the formula to exactly the same number of rows in Sheet1. (For eg: if my sheet1 has 15 rows, I need to copy the formula in A1:G1 of sheet2 till A15:G15). Hope someone help me Thanks in advance Toms --- Message posted from http://www.ExcelForum.com/ Toms You could add an unused row with a number or letter in it and then count that separately each time with countif? John "SMILE...

Cross referencing named row with named column
I'm trying to find a way to cross reference a column named June with a row named Sales but cant figure out what the syntax is. I thought it used to be =June|Sales but it doesnt seem to work. Hi try =INDEX(A1:H20,MATCH("June",A1:H1,0),MATCH("Sales",A1:A20,0)) -- Regards Frank Kabel Frankfurt, Germany "davide212" <davide212@discussions.microsoft.com> schrieb im Newsbeitrag news:917620B9-9182-45C8-9D0F-56B0878CF617@microsoft.com... > I'm trying to find a way to cross reference a column named June with a row > named Sales but cant figure out ...

Setting Up a Novel #2
I would like to write a book using Publisher 2002. How can I set this up to achieve optimum results in the shortest period of time? I need Table of Contents, Chapters, Page Numbers, Graphics, Formatting Styles, Headers, Footers, Glossary, Index, etc Is this the right application to use for this job? Thanks Barry Barry wrote: > I would like to write a book using Publisher 2002. How can I set this up to > achieve optimum results in the shortest period of time? I need Table of > Contents, Chapters, Page Numbers, Graphics, Formatting Styles, Headers, > Footers, Glossary, Inde...

removing empty cells in a rows of data
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I have may rows of data and I want to remove the empty cells. eg <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A B C D E F G H <br> 1 X X X X <br> 2 Y Y Y Y <br> 3 Z Z Z Z <br><br>I want it to look like this without having to copy/cut and paste. <br> &nbsp;&nbsp;&nbsp;&nbsp;A B C D <br> 1 X X X X <br> 2 Y Y Y Y <br> 3 Z Z Z Z <br><br>Is there any automated way to achieve this? Thanks Allan ...

Sum alternate columns over a large (>100) range
I need to sum alternate columns over a very large number of columns. Is there an easy way to do this? Sum(A1+C1+E1+G1 .... ) seems to bomb out after about 32 entries and I need more than this. Any Help, Thanks, Try instead, something like: =3DSUMPRODUCT(--(MOD(COLUMN(A1:Z1),2)=3D1),A1:Z1) or for all 256 cols A to IV (i.e. the entire row: A1:IV1) =3DSUMPRODUCT(--(MOD(COLUMN(1:1),2)=3D1),1:1) -- Rgds Max xl 97 --- GMT+8, 1=B0 22' N 103=B0 45' E xdemechanik <at>yahoo<dot>com ---- Kanga 85 wrote: > I need to sum alternate columns over a very large number of columns...

Set receive connector back to its defaults
Hello all I need to set the receive connector on the hub transport role back to its defaults. I am running the command set-receiveconnector -identity (name of connector) -permissionsgroup (groupnames) i can only add in one group, how can i add multiple groups to the command? What i am trying to acomplish is i dont want my hub transport server to repsond to a SMTP session unless its from the Edge server. I have tried configuring the "receive mail from remote server with ip" i added in the ip of the edge server but that didnt seem to work, as i can open up a telnet session t...

Color Scheme in Outlook 2007
Is there a way to change the theme / color scheme in Outlook 2007. When I installed it has defaulted to a light blue and yellow. Is there a way to change the colors being used in the app? Thanks, -Chris only to gray. it's done from word. From http://www.slipstick.com/emo/2006/up060525.htm#faq How can I change Outlook's colors? From the Office icon in the upper left corner of Word (replacing the File menu), choose Word Options, Personalize and switch color schemes. At this time there are only two colors to choose from - Windows XP blue and Vista slate gray. -- Diane Poremsky [...

network drawling set up
Hello, I am new to visio outside of setting up a basic network diagram. I would like to have a network drawling with just shapes diplayed, right now I have a diagram that is very cluttered with names, ips, product info, ect. And have the object info in a database so I only need to update newtork info in one place. And have the info linked to the shape in the drawling. Right now we have excel spreadsheets with multiple pages for info to our multiple sites. What would be easier to set up and update, excel or access database? Then I would like to save it as a web page to place on our i...

sending v cards, changing address settings #2
Hi i use outlook 2003, When I send a v card, the address fields change to businness, which is normally blank. I have set the home address as the address to use as mail address, but still this happens. I tried forwarding the contacts, not as v cards, which does not have the same problem, but found this duplicates the contact in my outlook. any ideas thanks tim ...