Alternate Row Shading

I'm a newbie who's got a five-column worksheet and about 900 rows, most of
them already typed.  Grateful if someone could advise how to shade all of
the rows in an alternate fashion, i.e., light-gray, then dark-gray, etc.

Thanks!
AB


0
Alex_Bachrach
10/4/2004 12:15:16 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
1061 Views

Similar Articles

[PageSpeed] 47

Hi
see:
http://www.cpearson.com/excel/banding.htm

--
Regards
Frank Kabel
Frankfurt, Germany


Alex_Bachrach wrote:
> I'm a newbie who's got a five-column worksheet and about 900 rows,
> most of them already typed.  Grateful if someone could advise how to
> shade all of the rows in an alternate fashion, i.e., light-gray, then
> dark-gray, etc.
>
> Thanks!
> AB

0
frank.kabel (11126)
10/4/2004 12:25:12 PM
hi,
see this site
http://www.cpearson.com/excel/banding.htm

>-----Original Message-----
>I'm a newbie who's got a five-column worksheet and about 
900 rows, most of
>them already typed.  Grateful if someone could advise how 
to shade all of
>the rows in an alternate fashion, i.e., light-gray, then 
dark-gray, etc.
>
>Thanks!
>AB
>
>
>.
>
0
anonymous (74722)
10/4/2004 12:35:25 PM
You can use Conditional Formatting for that, using 2 conditions.  One 
condition for odd numbered rows and another for even numbered rows...

1) Select the range of cells
2) Format > Conditional Formatting
3) Condition 1 > Formula Is, and enter the following formula:
=MOD(ROW(),2)=1
4) Choose your formatting for this condition
5) Click "Add"
6) Condition 2 > Formula Is, and enter the following formula:
=MOD(ROW(),2)=0
7) Choose your formatting for this condition
8) Click "OK'

Hope this helps!

In article <uML3dvgqEHA.3324@TK2MSFTNGP15.phx.gbl>,
 "Alex_Bachrach" <alex_bachrach(NOSPAM)@hotmail.com> wrote:

> I'm a newbie who's got a five-column worksheet and about 900 rows, most of
> them already typed.  Grateful if someone could advise how to shade all of
> the rows in an alternate fashion, i.e., light-gray, then dark-gray, etc.
> 
> Thanks!
> AB
0
domenic22 (716)
10/4/2004 12:36:03 PM
Thanks Domenic- worked like a charm!

AB

"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-F252A7.08360304102004@msnews.microsoft.com...
> You can use Conditional Formatting for that, using 2 conditions.  One
> condition for odd numbered rows and another for even numbered rows...
>
> 1) Select the range of cells
> 2) Format > Conditional Formatting
> 3) Condition 1 > Formula Is, and enter the following formula:
> =MOD(ROW(),2)=1
> 4) Choose your formatting for this condition
> 5) Click "Add"
> 6) Condition 2 > Formula Is, and enter the following formula:
> =MOD(ROW(),2)=0
> 7) Choose your formatting for this condition
> 8) Click "OK'
>
> Hope this helps!
>
> In article <uML3dvgqEHA.3324@TK2MSFTNGP15.phx.gbl>,
>  "Alex_Bachrach" <alex_bachrach(NOSPAM)@hotmail.com> wrote:
>
> > I'm a newbie who's got a five-column worksheet and about 900 rows, most
of
> > them already typed.  Grateful if someone could advise how to shade all
of
> > the rows in an alternate fashion, i.e., light-gray, then dark-gray, etc.
> >
> > Thanks!
> > AB


0
Alex_Bachrach
10/4/2004 1:38:53 PM
Reply:

Similar Artilces:

Excel should handle more than 65,536 rows.
We deal with large amounts of data and the current row limit of 65,536 repeatedly hinders us. This has been the limit for a long time and I don't understand why it hasn't been increased or eliminated. With today's computers it seems Excel should be able to handle much more. At the very least make it an overall number of cells limit, not an X by Y limit. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not s...

OnIdle() alternatives
Hello Newsgroupies, I am using a derivative of OnIdle() in the CWinApp class to service a series of multi-threaded CEvent/CMutex controlled data queues. However, whenever a modal dialog pops up or a menu item is selected or even a scroll bar is used the OnIdle() stops being called. Even if I use a WM_TIMER event instead to crank the servicing it does not always get through. And WM_KICKIDLE doesn't seem to be too reliable either. Can anyone recommend a better way to service these multi-threaded events so that general Windows GUI actions don't interfere with it? Many thanks in advanc...

displaying x number of rows on top
How do I display the first two rows all the time even when I scroll? Thanks. Clayton See answers in worksheet functions. Andy. "Clayton L. Wilson" <anonymous@discussions.microsoft.com> wrote in message news:5A67CA9D-6101-4BE6-8E8F-FB757985244F@microsoft.com... > How do I display the first two rows all the time even when I scroll? Thanks. Hi Clayton - move your cursor in cell A3 - goto 'Window - Freeze Panes' -- Regards Frank Kabel Frankfurt, Germany Clayton L. Wilson wrote: > How do I display the first two rows all the time even when I scroll? > Thank...

How do I move ot next row in macro
I have an excel macro where I need to tell the selection to move down one row from the currently select row. What is the correct syntax to do that? James Lysaght "JamesL" <forgeddaboudit@spamblock.duh> wrote in message news:z11se.4667$hK3.4443@newsread3.news.pas.earthlink.net... >I have an excel macro where I need to tell the selection to move down one >row from the currently select row. > > What is the correct syntax to do that? Hi James, ActiveCell(2, 1).Select However, it is rarely necessary (and is usually inefficient) to make physical select...

rows to columns
I have data in rows, I want to past to columns so i can import intio access... HELP Example (Current data) 3 42 42 3 38 38 4 22 I want to move to 3 42 42 3 38 38 4 22 THANKS Copy your range and do Edit > Paste Special > Transpose. HTH Jason Atlanta, GA >-----Original Message----- >I have data in rows, I want to past to columns so i can >import intio access... HELP > >Example (Current data) >3 42 42 3 38 38 4 22 > >I want to move to >3 >42 >42 >3 >38 >38 >4 >22 > >THANKS >. > ...

Fill in Blank rows with data from above
OK - So I have text in A1 Then column A is blank down to A(x) (x being and possible number. Then I have different text in the next row in A. A(x+1). This happens repeatedly through out about 50,000 records (that rules out using the auto fill) How can I populate ALL the blank rows in column A with the text that is above it? I know it is doable - but I just can't remember the procedure. Any help here will be appreciated. Thanks in advance dave There are instructions here to fill the blank cells manually or programmatically: http://www.contextures.com/xlDataEntry02.html Dave wrote...

How do I pivot a range of cells, i.e. from column to row? #2
How do I pivot/transpose the data from a range of cells from, say, a column to a row? I imagine it should be very simple, but I haven't found out how yet. For instance: row# 1 data 1 2 data 2 3 data 3 4 data 4 to column# 1 2 3 4 data1 data2 data3 data4 hope someone has an answer. Thanks! Copy then select cell where you want it to go and Edit/Paste-special, click transpose button. -- HansM "Ernst" <Ernst@discussions.microsoft.com> wrote in message news:DD03BE06-7DE9-456F-B42C-3580C1...

row that needs to wait till the next page break befor copying
I have a Macro that hides and copies visible cells to worksheet 3, but, I have a certain row that needs to wait till the next page break befor copying so that I don't have to manually adjust them. Moving them manually takes 25 minutes 2-3 times a day, if there is a way it would really save me. ...

Copy columns into rows
Can anyone advise me how to copy columns of contiguous cells, into a much easier to read row of contiguous cells? hi, copy the column. select a cell in column a. goto edit>paste special>transpose >-----Original Message----- >Can anyone advise me how to copy columns of contiguous >cells, into a much easier to read row of contiguous cells? >. > Don't forget, only 256 columns, which means only that many rows can be moved to a *single* row. Select the column(s), Right click in the selection and choose "Copy". Right click in the first cell of the row to rece...

Delete shaded cells
Hi. I have a rather large spreadsheet in Excel XP, and there are cells scattered throughout that are shaded pale yellow. I need to delete those from the sheet before performing some calculations. Can you help me with a code that will do just that, or do I need to manually go through every column and remove the shaded cells by hand? Thanks, Try this macro: Sub ClearYellow() Set rng = ActiveSheet.UsedRange Application.ScreenUpdating = False For Each cell In rng With cell If .Interior.ColorIndex = 36 Then .ClearContents End If End With Next Application.Sc...

Adding Blank Rows after the last Detail of the Report
Hi All, I've used this code from Dwayne: Private Sub Report_Page() Dim intNumLines As Integer Dim intLineNumber As Integer Dim intTopMargin As Integer Dim ctl As Control Dim intLineHeight As Integer intNumLines = 12 intTopMargin = Me.Section(3).Height intLineHeight = Me.Section(0).Height For Each ctl In Me.Section(0).Controls For intLineNumber = 0 To intNumLines - 1 Me.Line (ctl.Left, intTopMargin + _ (intLineNumber * intLineHeight)) - _ Step(ctl.Width, intLineHeight), , B Next Next End Sub My report needs to print blank rows (at least...

Rows repeating and want to delete easily
I have a .xls that is currently 65,000+ lines (a reporting tool). Man of the rows are repeating the exact information and I only want to us one of the rows. Is there a simple way to delete all but one of th rows without having to do it manually? I have tried a subtotal but no all of the cell information is then duplicated to the "Total" row. See attached small example. Any help is appreciated. Th +---------------------------------------------------------------- | Attachment filename: test ii.xls |Download attachment: http://www.excelf...

Column subtotals in the bottom row of each page
I'm working with an unfriendly project estimate worksheet that requires a project specific Bill of Materials to be individuaaly listed and priced each time. The BOMs are from pivot tables with additional colmns added for pricing. The tasks becomes a slow cut and paste or a "copy, special paste value and then cell= reference cell drag" process. Once this hurtle is jump I'm required to subtotal each page with a recap of each subtotal on a summary page....whew.. Is there a way to automate subtotals to pages (row: 45,90,135...)once the 2000 line items are in the work...

highlighting a row
is there a way to highlight a row depending upon where i am, fo instance if i am on cell C24 then the entire row 24 should b highlighted and so on i understand that it may not be possible to achieve this, so is ther any addin avalible which achieves this resul -- Message posted from http://www.ExcelForum.com Hi maybe Chip's Rowliner add-in is what your are looking for: http://www.cpearson.com/excel/RowLiner.htm >-----Original Message----- >is there a way to highlight a row depending upon where i am, for >instance if i am on cell C24 then the entire row 24 should be >high...

Highlighting Rows
I have a spreadsheet where every other line is highlighted (for ease o reading). When I sort the data, the colors also change position so I n longer have every other line highlighted. Is there a way to prevent this from happening? I would like th highlights to remain fixed but have the data in the cells sort based o my criteria. So, if row 1 is highlighted green, I would like it t remain green even if the data in that row changes. I hope I'm being clear. Thanks for any help -- Message posted from http://www.ExcelForum.com Nan, use conditional formatting for your highlighting, as deta...

Automatically insert row between groups of records
Hello! Can someone provide a suggestion for the following scenario? I have a list like the following (or I will, once I apply a macro solution I found in another post). I'd like to scan through the list, insert a row at each change in value in Column1 (column headers don't actually exist in my worksheet), and insert a lookup formula in Column 2 of the newly inserted row which uses the value in Column 1 to lookup a corresponding value on another worksheet. Column1 Column2 Column3 1000 Requirements 40 1000 Design 5 1008 Requirements 10 1008 Design 8 Run a macro that produ...

How can I delete rows that are hidden.
"Raquel G." <anonymous@discussions.microsoft.com> wrote in message news:ED5931BD-6BDA-4173-9373-D6154C669CEB@microsoft.com... > Unhide them first. Select the row before and the row after the hidden ones, and use Format > Row > Unhide. one way: Public Sub DeleteHiddenRows() Dim rDelete As Range Dim rCell As Range For Each rCell In ActiveSheet.UsedRange.Columns(1).Cells If rCell.EntireRow.Hidden Then If rDelete Is Nothing Then Set rDelete = rCell Else S...

Transpose from Col to row
Hi, I have one large sheet call a.xls which has data going down the column. e.g Hr target 8 10 9 12 11 9 12 15 14 20 Another worksheet on seperate workbook - calls b.xls, basic on matched data - hour, to transpose on to. The layout on the b.xls look like this between hr 8 - 9 9 - 10 10 - 11 11 - 12 12 - 13 13 - 14 Target 10 12 15 20 The 10, 12, 15 and 20 on the Target of b.xls is from a.xls If there is no matching data between a.xls and b.xls on the hour, just leave the field blan...

Repetative Row Deletes
I have a spreadsheet in which 4 of every 5 Rows need to be deleted. ie: Row 1 is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need to delete Rows 2-5, 7-10, 12-15, etc. I would prefer for the 1st row (ie: Row 2) be selected and then I can set a variable in the macro to tell it how many sets of 4 consecutive rows (with one good row between each bad set of 4) I need deleted. Help is appreciated as I am not a programmer but guys are really good. Thanks Danny Danny Not sure of the last bit or whether you want a programming solution, but if you just want to delete the four ...

How do I make first row sort in table
I am trying to sort a spread sheet and the first two rows won't. Hi, insert a blank row at the top and then sort "Annette Glenn" wrote: > I am trying to sort a spread sheet and the first two rows won't. Annette Glenn wrote: > I am trying to sort a spread sheet and the first two rows won't. How are you trying to sort? ...

Conditional Hiding of Rows or Columns
Hello Good People, I have a very large spreadsheet with approx 35 columns and over 50 rows. However, I do not need to see all the rows at any point in tim and may want to look at just a limited number of rows. Generally I nee to look at only those rows that have a non zero value in a specifi column. Such rows can be randomly dispersed within the spreadsheet. I can use the option of sorting the full spreadsheet based upon th value of the cells in the particular column, however, this does no help very much as I would need to do this every time I referred to th sheet - which may be upto 20 time...

Column format to override row fomat in FrX
In FrX the row format overrides the column format for font styles eg. bold etc. it would be useful if it was possible to have the option for the column format to override the row fomat ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesss...

Increase columns, decrease rows
Newsgroup, I need to increase the number of spreadsheet columns, and would gladly trade in rows. Anyone know how to do this? Thanks in advance -- Dave zdave83@comcast.net eBay??? The number of rows is set at 65k and the number of columns is 256. No trades allowed. Dave wrote: > > Newsgroup, > > I need to increase the number of spreadsheet columns, and would gladly trade in rows. Anyone know how to do this? > > Thanks in advance -- Dave > zdave83@comcast.net -- Dave Peterson ec35720@msn.com Hi, Is there any way you can switch the rows for columns and vic...

Pivot Table VBA code to move a row
I have a pivot table, and within the row field, I would be able to writ a macro to move a certain row to the bottom, regardless of the number o rows. Currently, if I move the row to the bottom while recording th macro, it will list the position number, such as ActiveSheet.PivotTables("PivotTable2").PivotFields("Carrier").PivotItems _ "Other").Position = 8 I found that I will not get the result I want unless there are exactl 8 items in the row section. Does anyone know if there is a way to mak sure that it moves to the "Last" position? Thank -- uc...

Column vs Row
How can you make Excel to advance one row instead of one column? For example you in A1 you have: :=D1 One after draggin it one cell below (A2)i would like to have :=E1 instead of :=D2 any clue?? I don't believe copying the way you want is possible with built-in Excel tools. I've had the same need as you though so I wrote this macro several years ago and it has met my infrequent needs for what I call "transpose copying". I don't even remember which situations it will work in and which it will fail in, beyond simple ones like your example. So for what it's...