Lock cells from user data entry based on certain condition

[sa02000]

I am using conditional formatting to gray out certain cells based on values in some other cells. But I would also like to somehow lock those grayed out cells so that no data can be entered in those cells.

As an example, I have table from a1 to d5. I am applying conditional formating in a1 to d5 range based on values in a7 to d11 range so that cells corresponding to zero values are grayed out in a1 to d5 range. Now I would also like to lock those cells in a1 to d5 range corresponding to which there were zero values in a7 to d11 range.

Is it possible? If so, would appreciate any help.

Thank you,
Jay

[teylyn]

Hi

without using VBA a solution could look like this: Since you already use a condition to format the cells, you can use that condition as well in data validation.

For example, you have a cell B2 that is conditionally formatted when C8=15. You also want to lock B2 for editing when the condition applies. So, for B2 you can use the following data validation. Click Data - Validation, select custom and enter

=IF(C8=15,AND(B2>0,B2<0),TRUE())

If the condition is met, any entry in the cell B2 must be both smaller AND larger than zero, otherwise an error is triggered and the entry rejected. Since no entry ever is both smaller AND larger than zero, this will always result in an invalid entry, effectively locking the cell from any entry, including text.

This may not be the perfect solution for a large and complex worksheet, but it is a quick and dirty effective solution without any programming required.

HTH

[sa02000]

This would be a great solution but I am actually using a Data Validation already (list option) so that user can only input few values only. I am getting a summary in separate sheet for those few values. Any other thoughts?

Regards,

[royUK]

There's code in this postthat locks cells, you should be able to amend it to work for you