how can I lock cell colors but not contents

[StansChem]

Hi,

I am making a request form in Excel. People I work with need to fill out the form and email it to me so I can pull samples for them. I want every other row of a portion of the form to alternate colors.

The problem I am running into is that when the users fill out the form, they move cells or rows around and screw up the alternating colors. I have looked for and found ways to lock a cells content but what I really need is the ability to lock the formatting of the cells without locking the contents.

I've attached an example of the worksheet and a common problem that arises when users fill the form.

Thank you!

[Croweater]

You could try;

Select the cells you want the format protected for.

Format Cells>Protection. Uncheck the locked checkbox.

Then Tools>Protection>Protect Sheet. Check Select Locked Cells and Edit Objects. Uncheck Format Cells

This won't stop them copying and pasting stuff to screw up your formatting. You could try to educate them just to paste values?

You could use vba too to ensure the colours remain as you want them, but (I suppose) you would need to post this query in the other section of the forum.

[LJMetzger]

Hi Stan,

One way to do what you want is to do all data entry using a UserForm. The user would press and 'Update Data' CommandButton on the UserForm to transfer the data from the Spreadsheet to the UserForm. The CommandButton code would unlock protection, put the data in the worksheet, and lock the data (and formatting).

This requires a lot of work, but the end product is relatively secure, and lends itself to selective data entry through ComboBoxes, ListBoxes, CheckBoxes, OptionButtons on the UserForm which can restrict user choices when necessary. It also can highlight errors when user data entry is incomplete and/or incorrect.

See the file associated with post #7 in the following thread for a simple UserForm example: https://www.excelforum.com/excel-pro...n-to-work.html

The code in Module1 opens the UserForm (change to modeless for easier viewing and inspection) and the UserForm module.

Lewis