coloring cells

I want to alternate colors between successive rows.  One blue one white, the 
next one blue and the next white and so on to the end of my worksheet.  It 
there a easier way than to hold the control key and click on each row?  


Question # 2 when I sort will the colors stay with the row or will they move 
with the sort.  Is there a way to get the colors to stay even when I sort?
0
6371 (1)
4/21/2009 11:58:01 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
486 Views

Similar Articles

[PageSpeed] 36

You can use conditional formatting which applies the colour using the Mod() 
function:

http://www.ozgrid.com/Excel/alternate-row-color.htm

After sorting, every second row remains the same colour as before sorting.


Aaron Blood has a Ledger Shade addin

http://www.xl-logic.com/modules.php?name=Downloads&d_op=viewdownload&cid=3

You may need to join.

-- 
Steve

"6371" <6371@discussions.microsoft.com> wrote in message 
news:9A57A76E-E50E-49C4-B148-4F12FBDA3D3D@microsoft.com...
> I want to alternate colors between successive rows.  One blue one white, 
> the
> next one blue and the next white and so on to the end of my worksheet.  It
> there a easier way than to hold the control key and click on each row?
>
>
> Question # 2 when I sort will the colors stay with the row or will they 
> move
> with the sort.  Is there a way to get the colors to stay even when I sort? 

0
AltaEgo
4/22/2009 12:17:06 AM
Select a gaggle of rows(not cells) the Format>Conditional Formatting

Formula is:   =MOD(SUBTOTAL(3,$A1:$A$2),2)=0

Pick your blue color and OK

Alternate row shading will survive sorting and filtering.

Or this version which will not shade blanks rows below your actual data.

=AND(MOD(SUBTOTAL(3,$A1:$A$2),2)=0,COUNTA(ROW())>0)


Gord Dibben  MS Excel MVP

On Tue, 21 Apr 2009 16:58:01 -0700, 6371 <6371@discussions.microsoft.com>
wrote:

>I want to alternate colors between successive rows.  One blue one white, the 
>next one blue and the next white and so on to the end of my worksheet.  It 
>there a easier way than to hold the control key and click on each row?  
>
>
>Question # 2 when I sort will the colors stay with the row or will they move 
>with the sort.  Is there a way to get the colors to stay even when I sort?

0
Gord
4/22/2009 3:26:32 AM
Reply:

Similar Artilces:

How not to display cell contents in toolbar above work area
I do not want the cell contents to be repeated in the toolbar above the work area. I have cells with long text messages and the display of box in the toolbar flows over the work area, which confuses readability. Can I switch this display off. One edits text in the cell anyway. Thanks Laurence Laurence, Go to the Tools menu, choose Options, then the View tab. There, uncheck the "Formula bar" setting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Laurence Lombard" <lombardm@mweb.co.za&g...

Is it possible to count the no. of characters in a cell in Excel?
In order to create Tweets in Excel, I wanted to know if you could count the characters/spaces in a cell. Hi, To count characters excluding spaces =LEN(SUBSTITUTE(A1," ","")) to count spaces =LEN(A1)-LEN(SUBSTITUTE(A1," ","")) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "karbrown12" wrote: > In order to create Tweets in Excel, I wanted to know if you could count the > characters/spaces in...

remove non-numeric characters from a cell
How can I remove all non-numeric characters from a cell? I am trying to figure final grades for all the students in our school. The cells that I am working with contain the percentage and the appropriate letter grade (i.e. 80 C+, 85B, 101 A+, etc.) The grades can be two or three digit numeric and one or two characters (may or may not have a space seperating the numbers from the leters). Thanks for any help on this, Scott Bodager (SWBodager@aol.com) Hi! Try this: Assume your data is in the range A1:A100. In B1 enter this formula and copy down as needed: =LOOKUP(1000,--LEFT(A1,ROW(IN...

Copying info from cells and putting them into a letter
I have a few columns in various sheets in a document, say columns A, B and C. The range of the cells within these columns is from 7:31. Now what I would like is for, in a new sheet, I am going to create a template letter sheet, "sheet13". Now everytime, I enter information in any of the cells within the columns, A, B and C, I want the information to be copied over into my template letter sheet, "sheet13". As the information will remain in my spreadsheets for a while, I only want the information copied over from new information which has just been inputted that day. I could ...

Double-clicking cell with =A1 no longer takes you to A1 in v2003?
I have a co-worker who tells me he used to be able to any cell and make it equal another using the "=A1" type of syntax -- and then when he'd double-click it, it would take him to cell A1. However, he says he cannot do it in v2003. :( Anyone know how to turn this feature on/off or know an alternative? Apparently it drives him crazy! :eek: ~Any help you could give would be greatly appreciated. :) TX!! -- GoDario27 ------------------------------------------------------------------------ GoDario27's Profile: http://www.excelforum.com/member.php?action=getinfo&am...

Print Preview colors
Hello, My app is using the standard Doc/View Print Preview function. The preview works fine but only the images are drawn not correct. I guess this has to do with the fact that the preview function is drawing them with 256 colours. Does anyone know if I'm correct and how to change this so that the images are drawn with more colours? Thanks in advance, Arno After further testing it appears that the problem isn't caused by the amount of colours but has something to the scale of the image. (CDC::StretchBlt function ?) When I draw an image at 100% the colour is good. What adjustmen...

Blank Cells....
I am using Excel 2007 on Win XP Pro SP2, How is it possible to do the following please? I have several columns formatted to currency. When the amounts in them are nil, they show up as follows: �0.00, I would rather they showed as an empty cell, is this possible? If so how do I accomplish this? I am only a novice with Excel, so please try to keep answers simple. Many Thanks for any replies. Office button (top left)>Excel Options>Advanced tab>Display options for this workbook>Uncheck Show a zero in cells that have zero value -- Kind regards, Niek Otten Microsoft MVP - Excel...

security of a cell
is it possible to protect a range of cells, so that it i wanted to change them a box could appear requesting the password to access the cells. i would idelly like to set this up so that the need to go through the top menu is bypassed. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ This was added in xl2002 as a built in feature. Before that version, you'd need a macro. And if the user opened the workbook without enabling macros, it wouldn't be effective. c...

In excel how do I increase a number in a cell by 1 after I print?
I use excel to print blank invoices for my company and i want that prints on the invoice to go to the next higher number after everytime i print. any suggestions? Take a look at Ron de Bruin's site for adding a number to a cell each time a copy is printed. http://www.rondebruin.nl/print.htm#number Gord Dibben MS Excel MVP On Thu, 26 Jul 2007 11:44:11 -0700, mjtaxpro <mjtaxpro@discussions.microsoft.com> wrote: >I use excel to print blank invoices for my company and i want that prints on >the invoice to go to the next higher number after everytime i print. any >s...

How do I count the number of brackets within a formula (cell)?
I am reviewing vehicle usage. An example of a formula under review is as follows: =(5-2)+(6-2)+(8-3). This will add up the kilometers. The 2nd column (the problem), I want to calculate is the number of trips. Each bracket is considered one trip. In the above example that would be a total of 3 trips and total of 12 km. One way, using a UDF: Public Function NumTrips(rng As Excel.Range) As Variant With rng If Not .HasFormula Then NumTrips = 0 ' or CVErr(xlErrRef) Else NumTrips = Len(.Formula) - _ ...

Coverting a series of Cells In a Column to Rows
I have a list of organizations with Name, address, email etc. separated by ===================. How do I convert these series of text in cells to rows? Here is an example unfortunately, each group does not have the same amount of cells. American College of Medical Quality 4334 Montgomery Ave 2nd Fl Bethesda MD 20814 301-913-9149 phone 301-913-9142 fax acmq@acmq.org http://www.acmq.org acmq@aol.com Ms Bridget Brodie Executive VP ========================== American College of Medical Toxicology 777 E Park Dr; PO Box 8820 Harrisburg PA 17105-8820 717-558-7846 phone 717-558-7841 fax hmiller@pa...

Displaying locked cells
I know that you can tab to unlocked cells, but when you have been designing a spreadsheet, is there a way of displaying which cells are locked (protected) and which are not? >-----Original Message----- >Mike > >I like to use a conditional format for this: > >In cell A1, call up Format / Conditional Format. In the drop down box select "Formula is" and then enter the following. > >=CELL("Protect",A1)=1 > >Select a suitable cell background colour (or font colour, or whatever) for the condition then OK. Copy your format to the rest of your...

HOw do I change group of cells from absolute reference?
When I do a Paste Special>Paste Link, the cell looks something lik ='MAIN'!$A$3. This is great, its just what I want. Now I remove the beofre the 3 and drag the cells so my list is linked correctly, like ='MAIN'!$A3, ='MAIN'!$A4, ='MAIN'!$A5, and so on. If I don't remove th $, it just stays as $A$3 for every item. Here's my question. Is there and easy way to convert the cells back t being locked with the $ in place again like: ='MAIN'!$A$3 ='MAIN'!$A$4, ='MAIN'!$A$5, and so on? Sometimes the list is long an its a pain puttin...

Gridlines disappear when applying background cell color
I haven't found a way to do a very simple thing - retain cell gridlines when applying a background color to cells. Surely there must be some endemic way to do this. As it is, sections of my pages have gridlines and other sections do not. I've gone Options/View/ gridline color, but even black gridlines do not survive applying a light b/g color. Thanks, p. -- MS Office '03 Yep. I'd turn off the gridlines and apply borders (under format|cells). Paul_B wrote: > > I haven't found a way to do a very simple thing - retain cell > gridlines when applying a background...

Append text to a cell in edit mode of excel sheet...
This is a multi-part message in MIME format. ------=_NextPart_000_006E_01C6A72A.1D066060 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I am unable to write/append some text to the cell (excel sheet) in edit = mode from my custom toolbar while I m able to write on complete cell = (not on edit mode) using following code=20 appXl.ActiveCell.Value2 =3D "Sample Text";=20 Can anyone help me for adding a text to a cell in edit mode of excel = sheet.=20 Regards,=20 -Harish Shinde=20 ------=_NextPart_000_006E_01C6A72A.1D0660...

I need a formula that will return the value of a cell next to a cell with searchable data. TIA
Thanks in advance guys and girls, I've created a spreadsheet that plays a game. Each row of the spreadsheet represents a new game with a different outcome. I need a formula in a cell that will search across that row, find a word "TRUE" and then display the contents of the cell one cell to the right of the cell with the word "TRUE" in it. FALSE Fred FALSE Fred TRUE Fred FALSE Sky FALSE Sky FALSE Sky FALSE Sky FALSE Sky FALSE Sky So i need the last cell (blank) in this row to return "Fred" cause "Fred" is next to the right of "True". I...

How do I get excell to highlight entire row when I am in any cell
I am trying to find a way to highlight the entire row no matther which cell I am in. renah7, have a look at Chip's addin here, http://www.cpearson.com/excel/rowliner.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "renah7" <renah7@discussions.microsoft.com> wrote in message news:DD8254BA-B3C1-418A-8B53-05C1408D1C8C@microsoft.com... >I am trying to find a way to highlight the entire row no matther which cel...

Is there any way to create a drop down calendar into a cell in ex.
I am trying to make an interoffice work order for maintenance issues and need to create a drop down calendar with a box with a button (icon) that when clicked on shows the calendar to pick the date and enter it into the box. I know how to insert a calendar that stays on the page but does not drop down. I am using Microsoft Excel 2003. Help!!!! See www.rondebruin.nl/calendar.htm -- HTH RP (remove nothere from the email address if mailing direct) "Heavensent1" <Heavensent1@discussions.microsoft.com> wrote in message news:71F54092-0CE9-43D7-ADDC-980E9ABF0227@microsof...

AutoSave without editing, or calculating cells without change
I am pulling data from another place which gets updated on save, however not just save, something on the sheet appears to need to be updated. I have written a Module which saves the sheet on a pre defined timed interval. The procedure is working (without editing the sheet). But my data will not update unless I manually change a cell anywhere on the sheet, then at the next timed interval of Save, all my tags are updated. How do I Change a cell (from within my code) each pass through the timer. I beleive if the cell automatically updated right before the save command, then my data ho...

Formattinb cells
Hi Is it possible to change the background color of a column of cells depending on the value of the cell at the top of the column. I have written a macro to do this but can't find a statement to do this: worksheets(:Sheet1").cells(cl,rw).backcolor = QBColor(14) Error message method not supported and nowhere in the help can I find an answer. Suggestions would be appreciated Thanks Charles Why not just use format>formula is>conditional formatting =a2=$a$1 and format as desired>copy format using format painter. -- Don Guillett SalesAid Software dguillett1@austin.rr.com...

Cell names #2
Is there a way to delete multiple names from the Insert>name>define box? One at a time. But you can get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager to help: You can find it at: NameManager.Zip from http://www.oaltd.co.uk/mvp e_vandenbush wrote: > > Is there a way to delete multiple names from the Insert>name>define box? -- Dave Peterson Hi E, To delete multiple names and many other name related utilities, try downloading Jan Karel Pieterse's invaluable Name Manager addin. It is downloadable, free of charge, at: ...

VB Code not grabbing cell values
hi, i was given the VB code below which takes all the value of cells V17 and X25 and create a list down columns A and K on the 'graphs' sheet, it works when i manually enter values into cells V17 and X25 and was testing it, but it doesn't work when those cells are auto populated with data as they calculate other cells, so the VB code isn't taking the values that appear in those cells automatically like it should. any ideas? Private Sub Worksheet_Change(ByVal Target As Range) Dim strColumn As String, lngRow As Long, ws As Worksheet If Target.Count <> 1 ...

Find a row based on a word in the cell
I would like to search a worksheet for "apple". When it finds "apple", I want it to select that row, as well as the row below it (which does not contain the word "apple".) I would like to continue to search until the end of the worksheet. So, it will find all rows that contain the word "apple", select that row, as well as the row directly beneath it. Then it needs to cut these cells and paste into a new worksheet. The rows will never be the same, but the word "apple" will always occur in column A. "apple" will neve...

how do I copy the worksheet tab name to a cell?
I've got a workbook with 12 tabs. Within one of the tabs, I'd like to create a table of the 12 tab names. When I change a tab name, I want the table to be updated automatically. I posted an example file to http://www.Galimi.com/Examples/shtChange.xls Following is the Workbook_Activate code Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh.Name <> shtPlace.Name Then Exit Sub For Each sht In ThisWorkbook.Sheets shtPlace.Range("nmSht").Offset(intr) = sht.Name intr = intr + 1 Next End Sub http://HelpExcel.com...

Text color in Win32 program
I just can't see why the following code doesn't change the text color to red. LRESULT CALLBACK WndProc(HWND hwnd, UINT message, WPARAM wParam, LPARAM lParam) { HDC hdc = ::GetDC(hwnd); HPEN pen = ::CreatePen(PS_SOLID, 3, RGB(255, 0, 0)); HGDIOBJ old; old = ::SelectObject(&hdc, pen); switch (message) { case WM_DESTROY: PostQuitMessage (0) ; return 0 ; case WM_LBUTTONDOWN: ::TextOut(hdc, 100, 100, TEXT("Hello Windows"), 13); ::SelectObject(hdc, old); return 0; } ReleaseDC (hwnd, hdc) ; return ::DefWindowProc (hwnd, message, wParam, lParam) ; } ...