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
973 Views

Similar Articles

[PageSpeed] 28

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:

how to paste formula and retain all or part of the cell references
I have following kind of problem: I want copy a cell from A5 to B5. In A5 I have a formula that takes values from A2 and E5, i.e. "=A2/E5". In the pasted cell the formula changes to corresponding values: "=B2/F5". In some cases this might be what I want, but in current situation I would like to change only column to corresponding - I would liket the resulting cell to include formula: "=B2/E5". How can I do this? Of course, in case of such simple formula it wouldn't be a problem but if I have very long formula with lots of references, it's a nightmare.. A...

summarizing worksheet data
Hi, I want to create what should be a relatively simple spreadsheet with, say, fifty worksheets in the workbook. Each worksheet would be identical in terms of number of rows & columns, as well as the type of data those columns contain. On the first worksheet, however, I'd like to be able to summarize the cumulative totals of all the other sheets. So, for instance, if cell a20 on worksheets 2 through 50 contained a formula that calculated a sum of the figures in a1:a19, I'd like to put a formula in a cell in the first worksheet that will total the cell a20 in all the other w...

Gathering and adding data from different rows
Hi all, I run a report at work every day that tells me how many shares of a company have been sold and from what year these shares were granted to the participant. The year and the amount of shares are on different lines. For instance, the year would be on D5 and the share amount would be on D10. This is a daily function and the data changes every day. I am required to manually scan these reports and add up the shares for each year separately. I have to separate the amounts for 2003, 1997 and 1996. I want to create a spreadsheeet that I can dump this data into and have the spreadsheet find...

collect data via email
We're looking for solution to collect intake data from multiple agencies (not on network), and get into our (currently 2003) Access database. With 2007, the 'EMail data collect' facility - can that collect and insert multiple rows with 1 email - or each email/form processes only 1 row..? thanks! don You will send a mail asking the user either to update a certain row or add information to a new record. So in both situations you are tied to single row processing... Secondly you have two options to choose from. Plain html which will present the user with a html emai...

Account locked out after 1 try
Hi! I do not know what's happening, but my password policy is by 3 tries. When I try to access my mail via Outlook my account get locked out after the first bad password entry. If I try to access via OWA there's no issue. Please help me! ...

Changing Resource and fields on a subform
I have a subform with fields on it that are present when i first open the form. But when I click on a status button, I'd like the subform to change the fields that are present. For instance when i first open the fields are formid, lastname, firstname, subject, date, range But when i click on formstatus button, i'd like the fields to show: formid, subject, range, product I know how to use recordsource to change the recordsource, but then the fields that I am not using show up as #name? How do I get the subform to change field names so that the fields I am not using, doesn't show ...

Show which cell has MAX, MIN values?
At the bottom of a couple thousand rows of data, I have =MAX and =MIN formulas. Is there some way I could make the cells beneath my MAX and MIN formulas show me the address of which cell has the displayed MAX or MIN value? At least the row number? Ed Ed, To return the row =MATCH(cell with Max or Min value,range starting in row 1,false) or to return the address, say, in Cell N3000, for a value given in N2999 =ADDRESS(MATCH(N2999,N1:NN2998,FALSE),COLUMN(N1)) or to return other matching information, like a name in column A =INDEX(A:A,MATCH(N2999,N1:NN2998,FALSE)) HTH, Bernie MS E...

Paste links, changed filename
I have created 3 exel 97 Files. Each File has approx 30 sheets. One of them is a source file. The other 2 use file1 data using paste link(paste special). At some stage I have changed the names of the files and now when I open them it asks the usual question " do you want to update linked information", Iclick "yes" ~ then the computer wants me to select the source file. Before, it just started straight into the file I wanted ~ how can i resolve this( with as little effort as possible)? Try hitting no, then do Edit / Links / Change Source, save the workbook and hopef...

"Change Credit Details" button does not appear
I would like to update my interest rates and credit limits monthly, but the "Change Credit Details" button does not appear when I go under the accounts' settings. In microsoft.public.money, mar11974 wrote: >I would like to update my interest rates and credit limits monthly, but the >"Change Credit Details" button does not appear when I go under the accounts' >settings. See if http://support.microsoft.com/kb/281109/en-us helps. I've no idea if it helped the original poster, but it seems I have the same issue, and it didn't help me. I'm ...

Access unwanted automatic data entry
when entering data in my Access table, if i use the tab ket to advance to the next field, it often enters a number in that field. Why does it do that nad how can I make it stop! Are entering data direcctly into Access table? If so, stop doing that, unless you only do it occasionally to repair data in database and you know database design and manage it. "Pat the biologist" <Pat the biologist@discussions.microsoft.com> wrote in message news:6B724974-C937-4C5C-BDBA-1A086C0712A1@microsoft.com... > when entering data in my Access table, if i use the tab ket to advance to...

Change height of row
Excel seems to limit the height of the row to some presets. Is there a way to set height of row to any height? Am trying to make spreadsheet line up with a pre-printed form. Hi i think the limit is 1 pixel -- Regards Frank Kabel Frankfurt, Germany "Pam MacT" <Pam MacT@discussions.microsoft.com> schrieb im Newsbeitrag news:CCAE9073-80C5-47B1-B103-7834241AF34B@microsoft.com... > Excel seems to limit the height of the row to some presets. Is there a > way > to set height of row to any height? Am trying to make spreadsheet line up > with a pre-printed form. ...

require cell completion in a form?
I have a very simple form in Excel. I'm not sure if it matters, but the machine I'm on uses Excel 2002, and most of the users are on Excel 2000. The first 2 questions on my form require that the user select from a list of names, and then select their relationship to that person. Then the users answer a series of questions about this person's job performance. I'm using data validation to manage the two drop-down lists - the names and the relationships. However, about a third of my users end up leaving one or both of these cells blank. I have a comment box set up as a remin...

Macro to pull every Nth row of data
I have a spreadsheet of data and I need to pull every 60th row out onto another sheet. Any simple macros? Thanks. Amy How about Sub Test() Source_Sheet = "Sheet1" Target_Sheet = "Sheet2" n = 5000 ' your last line of data on Sheet1 Target_Row = 1 Sheets(Target_Sheet).Select For nCount = 1 To n Step 60 Worksheets(Source_Sheet).Cells(nCount, 1).EntireRow.Copy Worksheets(Target_Sheet).Cells(Target_Row, 1).Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ F...

can i change the read receipt
This is a multi-part message in MIME format. ------=_NextPart_000_0108_01C6AD7D.3E5D58A0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable The read receipt by default set by outlook can be changed if yes pls. = let me know how ??? ITDuke ------=_NextPart_000_0108_01C6AD7D.3E5D58A0 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"te...

two checking accounts data downloads to just one of the accts
I have two checking accounts with our bank and when I download transactions into Microsoft Money for either account, all the transactions go to only one of the accounts set up in Money. I was prompted for the first download to our "main" account. Then when I went to download for the other account, I wasn't prompted where to download to... and all the transactions went into the first account. Did that make sense? My bank required we switch to Money from Quicken, so I am new to this. Tried to search in users guide and online for help. Help would be much appreciated - ...

Example using MFC WinInet classes with POST of FILE and other INPUT data
I am looking for a good example for POSTing both FILE and other INPUT data to a URL. I understand that the FILE must use a multi-part encoding but I do not understand how the atcual file contents get POSTed or how the INPUT data gets POSTed also. I am guessing the INPUT data can be added as part of the query parameters to the URL. ...

Average range of discontinuous cells
I have a block of columns (P-CT) that I need to average but only every 4th one. Example: I need to average P, T, X, AB, AF, AJ, AN, AR, AV, AZ, BD, BH, BL, BP, BT, BX, CB, CF, CJ, CN, CR. If I just type =AVERAGE(selecting these cells) it gives me 40238 when the only one with a value is cell P. These cells contain dates if anything. They are payment dates. I need the average number of dates it took to pay something. Any ideas for Excel 2003? Thanks. Never mind. I forgot to include my beginning date. It works. Just user error. "Nadine" wrote: > ...

Changing name from Arabic to English
I, m registering all new recruitment employees so always I'm writing the employee name by Arabic and English. So there is any code to change automatically the person name for Arabic to English ...

How do you turn off "Save a copy/Overwrite changes" dialog box
Hello Guys, I have created a (password to open) workbook using Excel 2003 SP1 on a shared network drive which when I try to save by using Ctrl S or clicking the Save icon gives me an irritating dialog box which asks: "The file 'filename.xls' may have been changed by another user since you last saved it. In that case what do you want to do?" -Save a copy -Overwrite changes Please can someone tell me how to deactivate this annoying dialog box as I have been unsuccesful so far in my efforts :confused: Many thanks in advance Nick -- Nicko -----------------------------...

OE locks up when I select "sent items" folder
I get the permanent hourglass when i click on the "Sent Items" folder. I have tried various steps to resolve, including a SP repair. All updates are installed. Any suggestions. larry Goode wrote: > I get the permanent hourglass when i click on the "Sent Items" > folder. I have tried various steps to resolve, including a SP repair. > All updates are installed. Any suggestions. Hi - This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. Y...

how to change business address
I can't seem to figure out for the life of me WHERE OR HOW to change my business address so my NEW address prints on my invoices now that I have moved. Can anyone help me with this? PLEASE????? LGLEIM In microsoft.public.money, LGleim wrote: > can't seem to figure out for the life of me WHERE OR HOW to change my >business address so my NEW address prints on my invoices now that I have >moved. Can anyone help me with this? PLEASE????? Is there a Tools->Settings->BusinessInformation place? YES!!! You did it! Thank you so much! "Cal Learner-- MVP" wrote: ...

Changing info from one worksheet to the next
Hi all. I have linked my 55 sheets to one which is great. What i need to know now if possible. Each sheet has the same question over 11 columns Each row is dated and a numeric number from 1 - 10 in each row Now on the master sheet where everything is linked, is there a way that If i changed the date on the master sheet it would reflect the answers from the row with that date? At present the answers showing is for 01/03/10, but i would like to look at the totals for 08/03/10 and show the answers from each sheet for that date. I could have a sheet for each week, but im hoping there i...

Last cell with data in a range
I enter weekly data into a spreadsheet with a summary page at the front. After every week, when new data is entered, I want the formula at the front to use the cell with the new data instead of me having to change the existing formula. For example, my data looks like this: A B C 276 300 421 175 0 0 0 I need a formula that will automatically detect the last number >0 in column C. Thank you for your assistance! =Countif(C2:C1000,">0") so your main page formula might be something like: =...

Data Validation in XL2007 suddenly stops functioning
I created a workbook in XL2003 that included some named ranges on Sheet2 that fed some data validation lists on Sheet1. Everything worked as expected. My colleague opened up the workbook in XL2007 and used it several times, and everything worked as expected. Then she called me, saying that it was broken. Of the 6 named ranges and related data validation columns on the other sheet, none of them were working anymore. I went over to her PC, and checked everything I could think of- I could set up working data validation test cells on Sheet2, but could not get the ones on Sheet1 to...

RMS should allow you to choose more than one item at a time
RMS should allow you to choose more than one item at a time, this would be helpful in creating PO's. Mutiple item selection is a standard in every software; you really dropped the ball with this one. ---------------- 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.micr...