Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel Worksheets

Posted on 2011-04-23
11
Medium Priority
?
256 Views
Last Modified: 2012-05-11
Attached is an excel worksheet.  Notice the yellow cells.  I want to protect all cells excepting those in yellow.  How do I do it.  I can protect the one yellow column of cells but not the others.
NewSalesWorksheetXtreme.xlsx
0
Comment
Question by:akbiro
  • 6
  • 5
11 Comments
 
LVL 2

Expert Comment

by:balatheexpert
ID: 35452742
first unprotect all

set format cells->protection->locked for all the cells except for the yellow cells

now protect the worksheet. your need will be done!

thanks,
bala
0
 

Author Comment

by:akbiro
ID: 35452748
Can't find set format cells...
0
 
LVL 2

Expert Comment

by:balatheexpert
ID: 35452779
select all cells

right click

select "Format cells"

select the last tab "protection"
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:akbiro
ID: 35452785
Let's try it a different way if you could.

1) protect the entire sheet.
2) unprotect those cells that are yellow.

How do I do that?
0
 
LVL 2

Expert Comment

by:balatheexpert
ID: 35452793
to protect cell level, u have to set the option i had mentioned.

select entire sheet.

right click

select "Format cells"

select the last tab "protection"

check in "Protected"

now select the column u want to unprotect

check out "Protected"

now protect the entire worksheet.

this protects those cells that has "Protected" and leaves out those dont have.

0
 

Author Comment

by:akbiro
ID: 35452806
So there isn't a way to set up something in excel that when I select Protect and Unprotect, it knows to protect any cell that is not yellow.  I have to go thru this routine described above each time I want to protect it.

0
 

Author Comment

by:akbiro
ID: 35452815
I want to highlight the yellow cells and click on a protect button...that is my specific question.
0
 
LVL 2

Accepted Solution

by:
balatheexpert earned 2000 total points
ID: 35452828
Yes u r right.

and just a change, the check box i mentioned is not "Protected" but "Locked"!

and anyways, the time when you are changing the color, u can change the "Locked" property also right?

coz, thought of writing a macro for this but this is not having a certainty. If i have to set "Locked" for those which are yellow, it has to check cell by cell. it will be a bit time consuming one.

but if you can give me any range such as u will be using only these many rows and these many columns (less number of rows & columns), then i can write a macro!!! otherwise, it will be a timeconsuming one!!!

-Bala
0
 

Author Comment

by:akbiro
ID: 35452830
I figured it out...Just click on one of the top yellow sales.

Then Review>Changes>Protect Sheet>
Click on Select Unlocked Cells

Works like a champ!!
0
 

Author Closing Comment

by:akbiro
ID: 35452832
THanks again!
0
 
LVL 2

Expert Comment

by:balatheexpert
ID: 35452847
cool! it should have been by default on your worksheet. select locked cells & select unlocked cells.

great..

enjoy!!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question