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
653 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:

FRX row format, MDA codes should allow me to use wild card charac.
FRX row format, MDA codes should allow me to use wild card characters, such as 11-1100-00,[Group.CODE???] 11-1100-00,[Group.CODE001]+11-1100-00,[Group.CODE002] -- Regards, Raja Raja, When using MDA Group and/or MDA Code within FRX you must explicitly enter the code IN FULL Use of wildcards is not supported, nor permitted. You will have to list each Code individually as you illustrate in your second line. Robert "Raja V" <rvijayarangan@hotmail.com> wrote in message news:84D34C61-13BC-42DF-B393-3F16E3E9C151@microsoft.com... > FRX row format, MDA codes should allow me to...

Data entry creating duplicate rows in table
I have a large form "JobTrack" that has a record source of the table "Job Information" I also have a subform "Tapered-Misc Form" on that large form that also has the record source "Job Information" The subform links as follows: Show Job Information for each record in Job Information using ID the subforms Data properties read as follows Source Object : Tapered-Misc Form Link Master Fields : ID Link Child Fields: ID Filter On Empty Master : No Enabled : Yes Locked : No When I switch from the main form to the subform and enter data, a duplicate rec...

locking up
This might be more of a GDI question, but since I'm in MFC, I'll ask: Would BitBlt-ing to the screen repeatedly tend to lock up a program, after alt+tabbing to another application? If so, why? This seems to be happening to me, and I want to get to the bottom of the problem. What I have is either a fade loop or a screen-size redraw, both based on a timer and bitblt. Quite often, after I leave the program to go to another app, I cannot return and must "end task". "Steve Russell" <srussell@removethisinnernet.net> wrote in message news:%23YFL5Ks%23F...

Change Field Caption
Is there a way to change the text on field labels? For instance, I want to use the Requested By field in POP for something else. Can I set GP to replace the text “Requested By” with something else across the board, and in SmartList? Thanks. Scott Not sure about SmartList but changing the text field to say Requested By is very simple. Open the POP window... go to tools - customize - modify current window (depending on your gp version) and change it. Then grant access to the modified window. "Scott Ferrar" wrote: > Is there a way to change the text on field labels? ...

Comparing two sets of data
I need to compare sets of data in two different spreadsheets in two different columns labeled the same, Grant Number. We are trying to track if both sets have the same grant numbers or if there are duplicates. Did you adjust the ranges from column Z to whatever column held your data? (and did you remove the extra trailing close parenthesis?) Another formula that I use is this: I insert a helper column to the right of the Key data. Then I use a formula like this in Grant1 (assumes data is in column B of both sheets). =IF(ISNUMBER(MATCH(B2,Grant2!$B$2:$B$9999,0)),"Match",&quo...

Capturing date changes
Hi I have an excel spreadsheet let me set up this example: Column A Column B Client Name Date Called ABC XYZ Situation if I call ABC on 5/16/89 but I change the date the next day to 6/16/90 I need to maintain in another sheet all of the dates I contacted ABC for purposes of reporting. I could use your help thanks One approach -- though awkward and CPU-intensive -- would be to tie into the Worksheet_Change() event. You could use the 'Target' parameter to determine if the change was in the column (and/or row) you're interested in and, if so, cop...

sql server data to ms word using xml
hi i was given a task to display sql server data in ms word using xml. Im currently doing research to accomplish the task. What i have in mind is to create an xml template (or schema?) to load the sql server data first.Then add the xml template in the ms word add-in option. Is it possible? Can anyone point me to the right direction? Thanks My approach to this for the last few years (since Office XP) has been to save a Word document formatted they way it should end up - as XML. And then rename it to a XSL and put in iterators to populate the data from the XML queried out of SQL Serve...

Desktop Alert Locks Computer
Greetings, I am seeing a little quirk on some of my desktops. It only happens occasionally, but it happens to different models of PC. Not all the time, but once in awhile, I have users that complain of their PC freezing. The only remedy is to do a hard reboot. I have noticed in these instances, that the desktop mail alert ( the preview that pops up by the system clock when you get a new mail) is showing when it froze. If I go into Outlook and turn off the desktop alert, then the PC freezing stops. So, I know that the Desktop Alert is the cause of the problem. Has anyone else ever seen this? Th...

import data to specific columns
Hi- I'd like to import the data in my tab-separated text file into *certain* columns of my spreadsheet. Excel imports the data into adjacent columns, but is there a way to import into non-adjacent columns; e.g., import data into columns A, D, and G? Thanks. -- marlea ------------------------------------------------------------------------ marlea's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26209 View this thread: http://www.excelforum.com/showthread.php?threadid=395159 How about importing them to a different worksheet, then copying the values where y...

Lock formula to cell
I want a formula to lock to a cell so that when I add or delete a column the formula will always remain with say column H I know if the sheet is Password protected this will prevent this happening but that is no use when I have to unprotect the sheet to delete a column. Anyone got thoughts on this? regards Pat Hi Pat without using VBA you can't achieve this Frank Pat wrote: > I want a formula to lock to a cell so that when I add or delete a > column the formula will always remain with say column H > > I know if the sheet is Password protected this will prevent this >...

Contents automatically entered into another cell
working on an estimate and trying to figure out a way to take the results of one cell and have its contents be automatically entered into another cell. I have a subtotal line item for a particular category, I am wanting to create a cover page showing the various catergories but not all of the breakdown. (It is simple to keep track of right now, but as the estimate gets more involved, changes become harder to remember). I'm trying to create a reference to the cover page that will reflect the sub-total line item from the detailed breakdown. So, if $3.23 is shown in the deatailed br...

Can custom headers be linked to a cell in the worksheet?
This was a feature in 123 where header or footer information could be retrieved from a cell in the worksheet. Is there anything similar in Excel? You could use a macro... For instance, this code goes behind the ThisWorkbook module: Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) With Worksheets("sheet1") .PageSetup.LeftHeader = .Range("a1").Text End With End Sub Texas_Toast wrote: > > This was a feature in 123 where header or footer information could be > retrieved from a cell in the worksheet. Is there anything similar...

Keeping text within the cell
Hi all, Mr. Newbie here, How does one format the cell so that when text is entered it does not flow across the worksheet or resize the cell? I would prefer to keep the cell the same size and not have the text run into the next column and then just mouse over the cell to read what is in that cell. Is this possible? Thanks Tim Foster Make an entry in the cell to the right (even a space). In article <rie3d059vtotqt4pihh14u0n6r34mqau1t@4ax.com>, Tim Foster <opyis@shaw.ca> wrote: > Hi all, Mr. Newbie here, > > How does one format the cell so that when text is entered i...

cleared transactions change to reconciled
I am using Money 2003 Standard. The "Tools/Options/Online Services/Automatically mark downloaded transactions" box is not checked. I mark a transaction as 'cleared', close Money, and shut down. The next time I open Money, all 'cleared' transactions are marked as 'reconciled'. Any suggfestions? Thanks in advance! ...

Open to Blank Pivot Table Data (Excel 2007)
I am wanting a user to open a file to a blank pivot table that has all of the columns and rows defined, but they have to refresh the table for data to appear. In PivotTable options I have: * unchecked to save source data w/ the file * set the number of records to retain per field as none However, when someone opens the file I send them it is still showing data. However, if I try to interact w/ the data I get an error the table was saved w/o underlying data so I will have to do a refresh. Therefore, my previous two settings seem to be working right except for it retaining a sn...

Locked out of database
Not sure what happened but get a message saying "database has been converted from a prior version of Access by using DAO compactdatabase and is now in a partially converted state." It says I should be able to import tables into a new data base but when I try this it just gives the same message about converting and the data base just doesn't open. Please help because lots at stake, have to do a report based on these tables so am waiting with anticipation!!!x Hi Pete, Usually this means you are opening an earlier version of Access in a new version. Make a copy of the data...

Remove white border around a color document
I have a document that is supposed to be brown. When I print, a white border appears arround the entire document. How can I remove this white border and get the full brown sheet? I've removed all borders in page layout but it still appears. Thanks Mike Many printers do not print to the edge of the paper. I believe that is the "border" you are seeing. There are several ways to get around this such as printing on larger paper and then cutting it to size, sending the job out to a commercial printer (who might do the same thing), or using colored or preprinted pa...

locked excel File ??
Hi . I am working with my colleges with one Excel file in a shared file system. Sometime - when I open the file, there is the message "locked from College" - but he is definetly out of office and shut down his PC. We are using Excel 2003 + Excel 2007. What can I do?? THnks for help Susan ...

how may I copy color of a other cell
Hello, I need a function that copy also the full format/color/size of a cell Example A1 = USD 12'500.00 (monetary size, 12 fat, blue bottom) A2 = mon 12.03.2004 (dates, 10 not fat, white bottom) If in A3 I type: =" A1 " I would like a blue bottom, 12 fat, etc. and If I type =" A2 " a white bottom, 10 not fat, etc.... Is it possible? Thank you for any help. Thierry, Maybe you can use the Format Painter. Select the cell that has the formatting you wish to copy, and click the Format Painter. It's a paintbrush on a button on the Standard toolbar. Now click the c...

Outllook2007 to open my Outlook2002 Win7 says files locked.
I'm following 2002 to 2007 instructions step by step. When I point Outlook to my ..pst file, I get access restriction messages. How do I make sure these files can be accessed by Outlook 2007? Thanks. Which exact steps are these? From your Outlook 2002 machine, copy the pst-file to your Outlook 2007 machine and place it in a convenient location such as My Documents\Outlook\ In Outlook 2007 you can then reconnect to the pst-file via; File-> Open-> Outlook Data File... For details see; http://www.howto-outlook.com/howto/backupandrestore.htm -- Robert Sparnaai...

get an average of 3 cells, if they all have numbers, in excel.
In excel I need to average 3 cells, only if they all contain a number. and if they don't contain a number, don't display an error. "rudyjudy" <rudyjudy@discussions.microsoft.com> wrote: > In excel I need to average 3 cells, only if they all contain a number. > and if they don't contain a number, don't display an error. =IF(COUNT(A1,B2,C3)=3, AVERAGE(A1,B2,C3), "") Unfortunately, you will not see this response unless you use a different NG interface, e.g. Google Groups or a newsreader that access the MSnews server, msnews.microsof...

How do I change the name of Access 2007 Database?
Unable to rename or delete Access 2007 databases created for practise. Can anyone help? On Nov 22, 3:56 pm, leroyd <ler...@discussions.microsoft.com> wrote: > Unable to rename or delete Access 2007 databases created for practise. Can > anyone help? What is the error message you are getting? navin "navin" wrote: > On Nov 22, 3:56 pm, leroyd <ler...@discussions.microsoft.com> wrote: > > Unable to rename or delete Access 2007 databases created for practise. Can > > anyone help? > > What is the error message you are getting? > > navi...

excel locks up after selecting a cell #2
excel locks up after selecting a cell. When ever, I select a Cell, that will automatically selects all the cell and this freezes the entire computer. Can any body who would help me resolve this issue? Please help.... ...

Joining/Merging Cells
I want to merge two cells into one and then, eliminate the 2d cell, e.g., Cell A Cell B Smith James Jones Fred CELL A now becomes Smith, James Jones, Fred I know about =Ax&", "&Bx [where x is the row number]; however, I do NOT want to keep column 2 after I complete the merger. However, if after I merge, and obtain the result I want, I don't know how to retain the result after I delete Column B. So, "adding" effectively adding the two columns together doesn't cut it. Thanks! Ann just select the merged data, Copy ...

Access table, data lost
When taking a regular back-up copy I noticed that the size of my database had halved, from 47MB to 23.5MB without any apparent loss of data. I now see that one table is approximately 25% of its original size , where the list of names now only reaches "B" instead of "Z" as before? Ouch! You may have actually lost data, or you could be lucky. This can occur when an index goes bad. If so, you may be able to rescue the data by deleting the index, and re-creating it. Details in the 2nd symptom in this article: http://allenbrowne.com/ser-47.html#NumberOfRecordsVaries B...