Locking cell color while allowing data changes in cell

In excel 2000, I created an attendance worksheet for my
classes.(Alphabetized names down left vertical column.  Dates across
top of horizontal row.)  I added a different color to all cells in
every other row to make for easier reading of each student's name and
absences.  Every other row stays with a white background.

My question:  I wondered if it was possible to lock row colors while
allowing data to change on top of them.  If a new student is added to
my class in alphabetical order, the alternating color pattern is often
lost. It is a pain to rechange row and cell colors.
Any shortcut available?  Any way to protect color rows but still be
able to change data?
0
9/4/2003 11:25:58 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1014 Views

Similar Articles

[PageSpeed] 49

Barb

Select the rows(not cells, rows) you wish to format.

Format>Conditional Formatting>Formula is  =MOD(ROW(),2)=1

Format>Pattern  pick a color>OK your way out.

Deleting and/or inserting rows will not disrupt the alternate row shading.

Gord Dibben  Excel MVP XL2002

On Thu, 4 Sep 2003 19:25:58 -0400, Barb <Barb.t98zz@excelforum.com> wrote:

>In excel 2000, I created an attendance worksheet for my
>classes.(Alphabetized names down left vertical column.  Dates across
>top of horizontal row.)  I added a different color to all cells in
>every other row to make for easier reading of each student's name and
>absences.  Every other row stays with a white background.
>
>My question:  I wondered if it was possible to lock row colors while
>allowing data to change on top of them.  If a new student is added to
>my class in alphabetical order, the alternating color pattern is often
>lost. It is a pain to rechange row and cell colors.
>Any shortcut available?  Any way to protect color rows but still be
>able to change data?

0
gdibben (257)
9/5/2003 1:49:35 AM
What I would do to insert a student after you have the 
formatting the way you want it is this.  Instead of 
inserting a row to add a student, copy all the students 
below where you want the new student and then do Edit-
>Paste Special->Values to paste all the students down one 
row.

What this will do is to copy all the data down but it will 
leave the formatting.  The only thing you may have to do 
then is to format the extra row at the bottom unless you 
have formatted the entire worksheet with the every other 
row highlighting.

Hope this helps.

Jeff

>-----Original Message-----
>In excel 2000, I created an attendance worksheet for my
>classes.(Alphabetized names down left vertical column.  
Dates across
>top of horizontal row.)  I added a different color to all 
cells in
>every other row to make for easier reading of each 
student's name and
>absences.  Every other row stays with a white background.
>
>My question:  I wondered if it was possible to lock row 
colors while
>allowing data to change on top of them.  If a new student 
is added to
>my class in alphabetical order, the alternating color 
pattern is often
>lost. It is a pain to rechange row and cell colors.
>Any shortcut available?  Any way to protect color rows 
but still be
>able to change data?
>.
>
0
9/5/2003 12:07:38 PM
Reply:

Similar Artilces:

named range, data validation: list non-selected items, and new added items
Greetings all- I'm using Excel2003/Win2000 I have a named range on Sheet2 that has a list of names I have a bunch of non-contiguous cells on Sheet1 that all have data validation that select from that list of names Two questions: (1) I want to use a range of cells at the bottom of Sheet1 to show any names from the list that were /not/ used in any of the data validation cells. Is there a straightforward way to do this without writing a separate formula for each name? I'd like the names to show up in adjacent cells, e.g.: unused: Name 7 Name 18 Name 31 and have that ...

Lock Taskbar movement
Is there a way to lock the taskbar so users cannot move it around -- Message posted from http://www.ExcelForum.com Not an Excel setting, but a Windows setting. If running Windows XP............. Start>Settings>Taskbar and Start Menu>Taskbar>Lock Taskbar. Gord Dibben Excel MVP On Sat, 14 Feb 2004 08:12:34 -0600, PRINCE21 <<PRINCE21.11mduw@excelforum-nospam.com>> wrote: >Is there a way to lock the taskbar so users cannot move it around. > > >--- >Message posted from http://www.ExcelForum.com/ ...

Worksheet Changes
Hi All, HYCH Have a worksheet that has data in the range A5:I105, would like cell a2 to show a date that any of this data has changed, was thinking of using a worksheet change_Event to manage this but not sure of how to go about setting this up, have used the simple change event for a single selection i.e A1 or B4 but not with a range area. Any help would be Great Steve A bit more explanation along with layout and what desired output looks like. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Steve" <instructorf@hotmail.com> wrote in mes...

Record Locking
I have a user who has a database that was converted from Access 97 to 2002. Now whenever a user types data into a form that is bound to a table, the error "the record is locked" comes up. This is a simple database, has some macros, but no VB code authored by any user. This error only shows when 2 or more users are in the database, but they are not updating the same record. I went into Tools -> Options -> Advanced -> No Locks is checked. Would installing SP2 help? Thanks! Shelley ...

Cell comments
I have added cell comments to my worksheet, but they have always bee static, meaning if you put your cursor on that cell it always display the same text. Is there a way to create some sort of comment tha would change depending on the information in the cell? When the curso passes over the cell, the code would go pull in data from a MS Acces table using the value in that cell as criteria -- LA ----------------------------------------------------------------------- LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=965 View this thread: http://www.excelforum.co...

RE: MSCRMDeletionService -- change frequency...
i am trying to modify the frequency for the MSCRMDeletionService...there is a registry value in the HKLM\Software\Microsoft\MSCRM\ key named DSRunInterval. one of our servers has this value as a REG_SZ (string) value while another server has it as a DWORD (number) value ; does anyone have an idea what format this value should be? and also is it based on seconds? thanks! sunish ...

Customise doesn't keep changes.
Hi!! I have Money 2005... I would like to customise some report in order to avoid some categories in reports. I did it for "Spending by Categories" report.. I ommited some and it worked great, howevver, when I return to home the report still show the categories I ommited and when I go to the report waht I customised is gone. Is there a bug in this? Can I create new report from nothing at all? or from a template? In microsoft.public.money, Marcell wrote: >Hi!! >I have Money 2005... I would like to customise some report in order to avoid >some categories in reports....

changing the delimiter while saving as txt file
Dear Experts, is there a way to change the delimiter to # instead of tab characters while saving the excel file to .txt file. Thanks in advance Hi see: http://www.cpearson.com/excel/imptext.htm -- Regards Frank Kabel Frankfurt, Germany "raj" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:845d01c48520$92fc1c20$a401280a@phx.gbl... > Dear Experts, > > is there a way to change the delimiter to # instead of tab > characters while saving the excel file to .txt file. > > Thanks in advance And some more links... Maybe you can steal some cod...

Formula to Autofill Info based on Other Data
Using: Excel 2000, on Windows 98 Good morning! I'm hoping that someone on here can possibly help me. I have basic Excel knowledge, but this is out of my skill set. My boss has given me a project to work on, in which I have a workbook with two worksheets that I am dealing with. The worksheets are labelled PRICE and SUMMARY. On the PRICE sheet, I have several columns. Column A lists the product name, Columns E list the per page charge for the *red* program, and Column G lists the per page charge for the *blue* program. Those are the columns we will be dealing with. All specific inform...

eliminating blank cells
I have two sheets in a workbook. Say sheet January and Summary. Sheet January looks like this DATE SN Amount TAX OVD CBS 12.01.2007 483 3464 1559 701 1184 13.01.2007 484 3907 1758 791 -627 14.01.2007 485 241 108 49 -75 17.01.2007 9/A12 1623 730 329 104 19.01.2007 11/A2 329 839 292 88 Now I want to gather these information in sheet "Summary" eliminating the blank rows, so Summary looks like this: DATE SN Amount TAX OVD CBS 12.01.2007 483 3464 1559 701 1184 13.01.2007 484 3907 1758 791 -627 14.01.2007 485 241 108 49 -75 17.01.2007 9/A12 1623 730 329 10...

OWA Change password option
I've read through your various postings and articles, relating to OWA change password option under a front-end / back-end 2003 SP2 senario. Please can you clarify the following steps I need to follow: 1) Create a new Iisadmpwd virtual directory in IIS on the frontend server. 2) Run the OWAADMIN tool, and select enable password feature on both the front-end and back-end servers. Can you clarify if by using the OWAADMIN tool, that I won't have to manually edit the registry on both servers as the tool will take care of this for me? Can you also clarify, which services (if a...

RUS does not allow mail boxes to be opened
When creating new user/mail box in AD on our Exchange 2000 server, the RUS generates a smtp address, but when user logs on Outlook reports 'Unable to open your default e-mail folders, you do not have permission to logon'. We have checked ADSI and the setting 'msExchUsserAccountControl' has a value of 'not set'. If you change this to '0', then the mailbox can be opened. Why is RUS not setting this value? We have tried creating new RUS and recepient policy and this does not make any difference. The user has permission to open his own mailbox. We have tried ...

Changing Functional Currency #3
Hi There is a live company ABC. We created the new company XYZ then I restore the live company database ABC to the newly created company XYZ by following the KB article 871973. Then we delete the all the transaction in XYZ company because we need the same company setup and masters with that we added required additional master data. The problem we are facing is we need to change the functional currency in XYZ company. How can we do this? Any one can help us Regards Nizham I don't believe you can change the functional in XYZ since there were transactions. Howabout creating a 123 com...

Can't change default print resulotion in Publisher 2003
In Publisher 2003, I tried to change the print quality via the "Printer Properties" dialog box, but that didn't change the print resolution. I clicked "Advanced Print Settings..." in the print dialog box, but the only drop-down choice next to "Resolution" is "Default," which doesn't say what the default is but seems to be set at Super-fine. How do I cange the print resolution to Normal or Draft? Thank you. Don Burgess <DonBurgess@discussions.microsoft.com> was very recently heard to utter: > In Publisher 2003, I tried to change...

I want a formula to ignore text values in cell references
If text is input into some cells referenced by a formula I want the formula to ignore whatever text it finds and just calculate the result of the numbers in cells. How do i do this? Presently the formula displays #VALUE! where there is text in one or more of the cells referenced. Use ISNUMBER(): In place of A1 use =IF(ISNUMBER(A1),A1,"") -- Gary''s Student "Russellrupert" wrote: > If text is input into some cells referenced by a formula I want the formula > to ignore whatever text it finds and just calculate the result of the numbers > in cells...

Changed relationship; records no longer linked
I have a Corporates table holding details of company customers. I have a CorpContacts table holding details of our contact persons at those companies. Originally I had a relationship (one to many) between Corporates.CorpName and CorpContacts.CorpName. I had a Corporates form which contained a CorpContacts subform displaying the contact persons' details. All working fine. But if we updated the name of the company i.e. Corporates.CorpName, the link was lost and the contact persons were no longer displayed. So I introduced new fields in each table ("CorpID"), i.e. some...

Changing OLAP Pivot Table Connection Source
I have numerous spreadsheets that have pivot tables pointing to an OLAP Cube via a connection string. Is there an easy way (either automated or manual) to change the connection string to point to a new version of the OLAP Cube? I can edit the connection properties and navigate to the new .odc connection file, but get an error saying "The OLAP provider returned an unexpected number of dimensions." ...

selecting rows of data
I have an excel worksheet with 4 columbs. I want to automatically select multiple rows of data according to search which will partly match one cell in the row and put the full ro as results in a new worksheet. for example 30/01/03,New York,233.85,complete 20/03/03,New Hampshire,188.22,complete 22/03/03,Texas,90.78,incomplete 23/04/03,Oregon,467.99,complete If the search string is "New" how can I produce a new worksheet whic will contain 30/01/03,New York,233.85,complete 20/03/03,New Hampshire,188.22,complete Any suggestions would be appriciated -- Message posted from http://w...

in Excel: How do I change "selected cells" highlight color?
When I select cells in Excel the shading is so light (blue) that I can barely tell which cells are selected from those that are not. Can I change the background color of cells that are selected? Thanks. Assuming Excel 2007 this is a known problem and as yet no patch for it. I don't have Excel 2007 but I have read that playing with your Contrast can help some. Gord Dibben MS Excel MVP On Tue, 10 Jun 2008 12:51:00 -0700, flameretired <flameretired@discussions.microsoft.com> wrote: >When I select cells in Excel the shading is so light (blue) that I can barely >tell whic...

How to make A1 the active cell in all visible worksheets ?
This is a multi-part message in MIME format. ------=_NextPart_000_0028_01CA8164.76257740 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable ......so that when i activate the sheet A1 is the cell in the upper left = corner of my screen. Thanx ------=_NextPart_000_0028_01CA8164.76257740 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 content=3D"text/html; ...

Conditional Formatting dependent on whether cell is populated.
I want to conditionally format cell G2 dependent upon whether there is a value in cell C2 AND G2 Therefore, if cell C2 has a value in it and cell G2 doesn't then cell G2 color = Amber if cell C2 has a value in it and cell G2 has a value then cell G2 color = Green Any helpful hints or useful suggestions? Thanks Tel Conditional Format of G2, condition 1: =AND(COUNTA(C2)=1,ISBLANK(G2)) format Amber condition 2: =COUNTA(C2,G2)=2 format Green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tel" wrote: &...

connecting cells with connector lines
Hello! Is it possible in Excel (2007) to draw a connector line (with or without an arrow ...) between two cells in a worksheet, so that the line-tips will follow their cells even when the latter are moved ? Thanks Michael On Feb 25, 7:00=A0am, Michaelprem123 <michaelprem...@gmail.com> wrote: > Hello! > > Is it possible in Excel (2007) to draw a connector line (with or > without an arrow ...) between two cells in a worksheet, so that the > line-tips will follow their cells even when the latter are moved ? > > Thanks > > Michael The line will automatically a...

Change Percentage DIV/0! Error
I'm trying to see the change between (A) 2002 unit sales and (B)200 unit sales, and am using the following formula: =(B-A)/A This gives me the difference negative or positive, however if A (200 sales) is zero, then I get the DIV/0! error, how do I fix this? Thank you -- Message posted from http://www.ExcelForum.com Hi, A couple of things you might want to consider. One is that your formula will not take into account what the change is if the prior month base amount is negative. To fix that use the =IF(base=0,"",(current-base)/ABS(base)) This should help >-----Or...

Summing items in a cell
How do you SUM items in a cell. I have a parts list with reference designators like U3-U6. I want the sum of the range of items, in this case it's 4, but can't figure out how to do this. Any help will be appreciated -- dpmoore ------------------------------------------------------------------------ dpmoore's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24920 View this thread: http://www.excelforum.com/showthread.php?threadid=384566 More info and then a formula using find can be used or a udf (macro for custom formula) -- Don Guillett SalesAid Soft...

2 different fonts in the same cell?
Hello! If I want (Times New Roman) "This is a red Wingdings 3 triangle ([change font] Wingdings 3: 123 [back to Times New Roman]").", I can do that in a cell. But if I refer to that cell (=A1) in B1, the Wingdings font doesn't come over and it winds up all TImes New Roman and looks like "This a red Wingdings 3 triangle ({)." How can I "=A1" for a cell that has two fonts in it and have the two fonts show up?? Thanks! VR/ Lost You can't have that in a FORMULA. You would have to change it to text first. -- Don Guillett Microsoft MVP Excel Sales...