Lock conditional formatting (to prevent tampering)

**LBExcel226** · 10-23-2012, 04:34 PM

Hello everyone....I like to think I am somewhere between a beginner and intermediate user in Excel.

I have this Excel sheet where I need to give each cell a unique conditional formatting (with 3 rules each). Basically it's a goal vs. progress set up. The progress cell is compared to the goal cell.

Every week, several co-workers are going to be filling this out the progress cells. I know that if you use paste special > values, it won't delete the conditional formatting, but I would like to know if there is a way to "lock" the formatting so that no one can damage it by pasting, etc.

As it is, I have already had to fix the formatting multiple times, and it's extremely painstaking. I have no idea why, but it seems like even the copies of the file where I have fixed the conditional formatting, it still seems to become corrupted somehow.

Any suggestions?

**Dave H9** · 10-25-2012, 05:41 AM

Yes,

Protect the spreadsheet (option is in the review tab), and make sure all the formatting options are unchecked , you can password protect if necessary.

Dave H

**LBExcel226** · 10-25-2012, 12:25 PM

Is there a way to protect the sheet so that they can enter new values into the cell, but the conditional formatting will not be changed? It seems that if I protect the formatting, no one can enter new values in the sheet.

**Dave H9** · 10-26-2012, 03:01 AM

YEs before you protect the worksheet select the cells you want the user to be able to edit, right click and select format Cells, then on the protection tab uncheck "locked".

Hope that helps

Dave H

**jtmann** · 07-30-2014, 01:42 PM

What if I want anyone to be able to enter into all cells, insert rows and paste items without botching up the conditional formatting?
My conditional formatting ranges get all funky every time a row gets pasted!!!

Help pretty please.

**Winon** · 08-01-2014, 12:38 PM

Hello jtmann,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**taylorsm** · 11-03-2016, 01:43 PM

thanks for killing the thread because his question was the same as the OPs but the response didn't address that.

**FDibbins** · 11-03-2016, 11:29 PM

Originally Posted by taylorsm

thanks for killing the thread because his question was the same as the OPs but the response didn't address that.

If you took the time to read the forum rules, you would see that that we require members to post a new thread with their question, and not attach their question to another member's thread.
We all understand that sometimes, a question is asked that is fairly similar to another 1 that has already been answered, but doing that could just cloud the issue for the OP, and responding members would not know to whom follow-up questions where addressed, nor to which question

**willow1985** · 08-19-2020, 04:02 PM

Originally Posted by taylorsm

thanks for killing the thread because his question was the same as the OPs but the response didn't address that.

I agree, I do not see anything wrong with what jtmann asked as the original question was not properly answered. I am sure I will not be the first or last google browser that will come across this thread looking for the alternative jtmann asked for only to be disappointed the thread ends here.

**FDibbins** · 08-19-2020, 10:57 PM

Originally Posted by willow1985

I agree, I do not see anything wrong with what jtmann asked as the original question was not properly answered. I am sure I will not be the first or last google browser that will come across this thread looking for the alternative jtmann asked for only to be disappointed the thread ends here.

Our forum rules are quite clear on this matter - perhaps if you took the time to read them, you would not have bothered to post here?

On the other hand, if you did read them, and did understand rule 4, then perhaps your concern is more with accepting our rules than with their appliction?