Excel 97 Worksheet Protection and cell colour

Hi there,

One of our users has setup a worksheet will a small range of cells that are 
locked (they have formulas in them), he then protects the sheet.

He then wants to change the colour of some of the other cells, these cells 
are not locked, but he cannot change the colour of the cells.

Is there an obvious solution?

Cheers,

Andy 


0
andy3161 (3)
11/9/2004 2:54:45 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
211 Views

Similar Articles

[PageSpeed] 55

Hi
AFAIK you can't do this in Excel 97 without first removing the
protection

--
Regards
Frank Kabel
Frankfurt, Germany


andy wrote:
> Hi there,
>
> One of our users has setup a worksheet will a small range of cells
> that are locked (they have formulas in them), he then protects the
> sheet.
>
> He then wants to change the colour of some of the other cells, these
> cells are not locked, but he cannot change the colour of the cells.
>
> Is there an obvious solution?
>
> Cheers,
>
> Andy

0
frank.kabel (11126)
11/9/2004 4:50:46 PM
I don't use xl97 anymore, but I could do this in xl2002 (with nothing special
allowed on a protected sheet).

I could copy|paste special|formats over unlocked cells on a protected worksheet.

You could put the colors/formats you like in a nice spot or the user could use
his/her own workbook/worksheet.

(I don't recall if xl97 behaved the same way--but you'll know after a short
test.)



andy wrote:
> 
> Hi there,
> 
> One of our users has setup a worksheet will a small range of cells that are
> locked (they have formulas in them), he then protects the sheet.
> 
> He then wants to change the colour of some of the other cells, these cells
> are not locked, but he cannot change the colour of the cells.
> 
> Is there an obvious solution?
> 
> Cheers,
> 
> Andy

-- 

Dave Peterson
0
ec357201 (5290)
11/9/2004 10:19:36 PM
And if you want more control, you could provide a macro that allowed only
certain changes.

You could specify the range and the colors.  And not allow anything else.

andy wrote:
> 
> Hi there,
> 
> One of our users has setup a worksheet will a small range of cells that are
> locked (they have formulas in them), he then protects the sheet.
> 
> He then wants to change the colour of some of the other cells, these cells
> are not locked, but he cannot change the colour of the cells.
> 
> Is there an obvious solution?
> 
> Cheers,
> 
> Andy

-- 

Dave Peterson
0
ec357201 (5290)
11/9/2004 10:20:23 PM
You can paste special>formats from another sheet to just the unlocked cells.

Not to locked cells.

Gord Dibben Excel MVP

On Tue, 09 Nov 2004 16:19:36 -0600, Dave Peterson <ec35720@netscapeXSPAM.com>
wrote:

>(I don't recall if xl97 behaved the same way--but you'll know after a short
>test.)

0
Gord
11/10/2004 1:12:29 AM
Reply:

Similar Artilces:

Does anybody know why these two Excel files must be open in the same process?
Hi all, I am doing the following sequence of operations all the time: 1. Open up an Excel 2003 sheet A; 2. Click a button and run a macro in that Excel sheet; 3. After it finishes running, open up another Excel 2003 sheet B in the same Excel session/process; 4. Press F9 to do calculation/update the sheet, and save the Sheet B. B has a lot links linked to A... ----------------- Sometimes, if I update file A, save it, and close it before opening B, (i.e. now the file B and A are not in the same Excel session/ process) , B's update will fail (i.e. B's links will not ge...

Duplicate entries in excel spreadsheet
I have lists of attendees to an annual event. I've taken the lists and combined them, and sorted them by first and last name alpha. I want to keep the names and corresponding collumns for those that attended my event more than once and dump the rest. Is there a way to sort them quickly as i have over 15,000 names. Cheers! I'd insert two new columns before the list, say A and B. In the first data row in B, say B2, I'd enter a formula that combines the first and last names to create a field the uniquely identifies each record: =C2&D2 and copy it down to all records. N...

Excel file size reduction
Can anyone tell me, how can a file size be reduced. For one possible problem (and solution) see http://www.contextures.com/xlfaqApp.html#Unused -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <fb5801c3f1c5$04b46b50$a601280a@phx.gbl>, anonymous@discussions.microsoft.com says... > Can anyone tell me, how can a file size be reduced. > > "Stacie Pierson" <anonymous@discussions.microsoft.com> wrote in message news:<fb5801c3f1c5$04b46b50$a601280...

What is the cell data reference for 5th row and 7th column called
Can someone help me with this question? I need to get the answer for my homework. Thanks for your help. I'm guessing, but might it be G5? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Shy-di@verizon.net" <Shy-di@verizon.net@discussions.microsoft.com> wrote in message news:A0979494-228B-435E-98F7-6BD26C900266@microsoft.com... > Can someone help me with this question? I nee...

EXCEL 97
Hi there! Ive created a linked spreadsheet in 2003 and am unable to open in 97. The error msg i get is "unable to open - insufficient memory". Any clues how i can get around this little dilema??? Thanks in advance! ...

Urgent Help Required on Excel Macro Problem
Hi there, We are facing one problem from around 2 months.Some of our users have XF_SIC.A virus found in their pc.XF_SIC.A virus generally found in excel workbook as macro virus.This virus create one excel 4.0 macro sheet with "very hidden and very protected" attributes in excel 97 workbooks which can't be removed as it is not seen.every time when infected excel 97 file opens it infect the default startup excel file(Book1 placed in XLSTART folder) and then every excel file that will be open will have infection as default file book1 infected.now our antivirus software mcafe...

Supplemental info to "HELP-excel date malfunction
Here's some additional info to the earlier post on the date cell function malfunction. Been isolated to the function to insert the date (cell format/date). It seems the application cannot interpret the system date properly. Always inserts/interprets the date as CurrentMonth-01-2011, instead of actual date. I cannot use the application this way. I am using the 'general' setting in cell format, then entering the string Month(abr)-day [eg= dec-17] instead of letting application enter the date automatically. Is there a patch available for this issue? -- Visit Family Rad...

Excel question ?
Hi, can anyone help me out with this .... I have excel 2003 table and sheet inside of it have some formulas. I would like to paste text data from notepad to that sheet without of erasing any of the containing formulas which are like I said already there. Thanx Hi Steve You could create a textbox from the ">View >Toolbars > Drawing menu, then paste your information in it. The textbox can be size and position any place on your sheet. HTH John "Steve" <steveaa@dzemail.com> wrote in message news:id0ci1$mh2$1@localhost.localdomain... > Hi, can anyone help me o...

Sheet Protection Problem
Hello All, Using Excel XP. I have 100 worksheets in a workbook where I have a range in each one that I have the cells protected. I am using VB code to protect and unprotect the worksheets. (see below). The problem that I am having is after unprotecting the sheets and then protecting them again using the VB code I can go to the protected cells and change the data (which I don't what to happen). I looked at the Protect Sheet Box where it says "Allow all users of the worksheet to:..." the first box in the group, Select Locked Cells is checked. I don't...

Cell Contains Data.
How can I tell a cell that IF a cell contains data (numbers, text, whatever) do x or y. How can I identify data?? I don't want to tell the function to look for a specific number or text, just data, just something in the cell... =IF(A1<>"","Data","No Data") "GEM" <GEM@discussions.microsoft.com> wrote in message news:07916799-F312-4E89-A926-59356B74ED89@microsoft.com... > How can I tell a cell that IF a cell contains data (numbers, text, > whatever) > do x or y. How can I identify data?? I don't want to tell the function ...

Active cell highlight
Hi, I would like to know if there was a way to have an active cell always in yellow or any color. Example: If the active cell is moved around, the active cell will be always in yellow. Thank you in advance maybe.... you may want to try Chip Pearson's Rowliner: http://www.cpearson.com/excel/RowLiner.htm E wrote: > > Hi, > > I would like to know if there was a way to have an active cell always in > yellow or any color. > > Example: If the active cell is moved around, the active cell will be always > in yellow. > > Thank you in advance -- Dave Pet...

Need to separate multiple numbers in one cell
I have multiple number in one cell and i need to somehow put each number into one separate cell for each number: I have : column A row1 2 5 8 10 I want: column B column C column D column E row1 2 5 8 10 How do I do it on Excel 2007? thanks EggHeadCafe - Software Developer Portal of Choice Map Stored Procedure Output To Class Properties http://www.eggheadcafe.com/tutorials/aspnet/2853a1aa-5db7-40d0-9cde-46847fa770ef/map-stored-procedure-outp.aspx Hi, Use Data > Text to columns and specify the delimiter as space ...

The value become wrong when get value by using AppleScript while other worksheet is active.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I just want to send a bug report. Summary: The value become wrong when get value by using AppleScript while other worksheet is active. Description: 1. Create an AppleScript file with below script. tell application "Microsoft Excel" activate set OriginalWorkbook to workbook "Workbook1" set OriginalData to string value of column 1 of row 1 of worksheet 1 of OriginalWorkbook end tell 2. Launch Excel 2008 and create a Workbook called "Workbook1". 3. Enter time data "11...

copy value in cell above to cell below for a whole column
I have a spreadsheet that was saved from a report. this report only lists the employee name once. There is a way to copy the value of the cell above to the cell below. I can get as far as highlighting the blank cells with the Go To function, then I can not remember the steps to tell excel to copy the data into the blank cells. Can anyone help? Ctrl+D will copydown the value of the top cell to the highlighted cells below. "tarthur" wrote: > I have a spreadsheet that was saved from a report. this report only lists > the employee name once. There is a way to copy th...

How can I remove the previous lock cells and keep the new ones.
Could you please help me?... I locked and protected the cells A1:H20 in the sheet 1 two months ago. However, yesterday I wanted to unlock and unprotect these cells and locked and protected the cells A21:H40 instead. However, for some reason the cells A1:H20 are still lock and protect. How can I remove the previous lock cells (A1:H20) and keep the new cells (A21`:H40) only. Thanks. Maperalia In EXCEL 2007 take the following actions to unlock cells A1 to H20:- 1. Home / Cells group / Format / Unprotect Sheet / enter the password to Unprotect Sheet / OK / highlight cells A1 t...

How do I enter a time formula into a worksheet that determines if.
How do I, set about entering a formula within a worksheet that determines if the Time is greater or less that the time in a certian cell; eg =if(now()>K17,"then display this","") K17 holding the Value 08:30 AM formated to time =IF(MOD(NOW(),1)>K17,"then display this","") HTH Jason Atlanta, GA >-----Original Message----- >How do I, set about entering a formula within a worksheet that determines if >the Time is greater or less that the time in a certian cell; >eg =if(now()>K17,"then display this","") ...

Hyperlink to hidden worksheet
I have a spreadsheet with many tabs, and am trying to keep things simple for the end users. Is there a way to hyperlink to or create a button w/macro that goes to a hidden worksheet? A hyperlink will not work on a hidden sheet. This code attached to a button shows the sheet "Salaries"... '-- Sub LetsSeeIt() Worksheets("Salaries").Visible = True End Sub '-- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "JoeleneW" <JoeleneW@discussions.microsoft.com> wrote in message... I have a spreadsheet with many tabs, an...

Counting specific test in a column in another worksheet that fall between entered dates
Hi, I'm trying to count the amount of times a txt string appears associated with a date on a different worksheet between two separately entered dates on the sheet were the calculation is being performed. Im using this formula but it only returns the 'value if false' even-though the logical test result is true i.e Derbyshire has been entered into the sheet on the 09/09/09 : =IF(('Tests 1'!$D:$D="Derbyshire"),(COUNTIF('Tests 1'!A:A,">="&$N$22)-COUNTIF('Tests 1'!A:A,">"&$O$22)),"") Thanks for any in...

Adding data from multiple cell and linking to a new worksheet
I have data in a sheet with names and amounts due. Like A 500 B 300 A 600 B 300 C 400 C 200 There is a second sheet named summary. I want the totals of each person to appear in the summary sheet. These should be linked cells and any change in data in sheet 1 should be updtaed automatically Create a PivotTable http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Dave Hawley www.ozgrid.com "Rashmi" <Rashmi@discussions.microsoft.com> wrote in message news:56DC0F3A-8D60-4FE2-B778-3A07790EFE8A@microsoft.com... >I have data in a sheet wi...

Excel Frequently Used Worksheets
I am looking for a funtion in Excel similar to the MS- Word menu option "WORK". In Word, this feature keeps a separate list of frequestly used documents that could be used a default docs such as a monthly report, letter(s), project headings/sections, etc. This list is separate from the standard 1-9 last opened documents. In Excel I see this feature as a great way to keep a similar set of spreadsheets that one must access frequently, but in the recently used list (1-9), could get rolled off. I looked through all the commands in Excel and could not find it at all. As far a...

Move cells down to match cells
A B C D E 1 000801025-8 009601025-3 2007 000801025-8 2007 2 000801026-9 009601026-4 2004 000801026-9 2004 3 000801028-1 009601028-6 2006 000801028-1 2006 4 000801036-8 009601036-3 2008 000801036-8 2008 5 000801044-5 009601044-0 2007 000801044-5 2007 6 000801044-5 009601055-0 2004 000801055-5 2004 7 000801055-5 009601064-8 2001 000801064-3 2001 8 000801055-5 009601091-2 2006 000801091-7 2006 9 000801064-3 009601095-6 2002 000801095-1 2002 10 000801091-7 009601097-8 1998 000801097-3 1998 11 000801091-7...

using data from another cell
I would like to type a name in one cell(1) then check in list (different column) if the same name is there, and if it is there get a value of another cell locate in the same row where. Can some one help me? Create your second list and use VLOOKUP as follows: Name is typed in: A1 List of names and values in: C1:D10 =VLOOKUP(A1,$C$1:$D$10,2,FALSE) "leo" <reani1996@hotmail.com> wrote in message news:OiO5fCTWDHA.2268@TK2MSFTNGP11.phx.gbl... > I would like to type a name in one cell(1) then check in list (different > column) if the same name is there, and if it is t...

'Save As' hangs Excel XP 2002 SP3
I have a problem in our office. For the most part Excel works fine. We can open files and save them as long as we dont use the 'Save As' feature and try to save it to a Network Drive. Files can be saved locally then have to be copied to the Network Share. Of course my first reaction was 'Permissions Issue'. BUt each user has full access to the folder. They can right click and create a new Excel file and rename it. BUt if they open that same file then try to 'Save As' back to the same folder Excel hangs. Using the 'Save' feature works just fine. ...

Converting excel files to doc files
Is it possible to convert an excel file over to doc format? Alvin, You can open an Excel file with Word, then save it. Word saves as doc files (if that's what you mean). Be sure to type the doc extension, or Word will overwrite your original xls file. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Alvin" <anonymous@discussions.microsoft.com> wrote in message news:0c2501c4316d$5e715b20$3a01280a@phx.gbl... > Is it possible to convert an excel file over to doc format? Dear Earl, Not good... My excel file...

How to exclude cells from a datasource?
Hi, I have an XY chart in excel97 that uses a range of cells for the data source. Every now and then I would like to exclude a XYnode from the chart. Without having to reselt the datasource. Is there a value one can enter in the cell that exludes it from the graph (compare: putting text in a cell excludes it from the SUM() function). For example point X Y a 1 1 b 2 2 c 3 3 Gives a graph with two lines connecting the three points While point X Y a 1 1 b ? ? c 3 3 Should give a graph with one line connecting point a and c. Point b is left out. Or is the...