Lock Black Colored Cells (Revised)

[j.stitt1]

Hello,

I am looking for some help to create a vba code that will lock the cells that turn black after picking a shape from a drop down menu in cells B5:B146.

Each shape has its own set if cells that change to color black.

If delete or change the shape item it automatically clears the rows content for cells C thru V and Y so I will also need it to unlock any cells that return to color white.

Thanks
Jeffrey

[Paul_Hossler]

1. Thanks

2. Can you attach a small sample workbook. The screen shot does not show such things as a "drop down menu in cells B5:B146" or the shapes


3. Also, it's a little hard to read and by attaching a workbook potential responder won't have to create any test workbook and will be more likely to a) answer and b) offer better suggestions


Do different shapes format different cells? If so, what are the rules?

Does you sample WB have the logic in to color the cells black already?

[j.stitt1]

Do different shapes format different cells? If so, what are the rules? Yes they have conditional Formatting.

Does you sample WB have the logic in to color the cells black already? Yes.


attached is the my actual workbook.

[Paul_Hossler]

I've always found conditional formats impossible to read and react to, so you might have to change your approach unless someone else here has a way to to it

Since it seems like 'Shape' Col B is the user's first choice on Input Page, I'd use Worksheet_Change to take that value and handle the rest of the row using VBA instead of using CF and a lot of complicated formulas

1. format any N/A cells in that row as (say) Black and Lock them (or maybe just use Borders to put an X through the cell
2. any User Input Required cells as (say) light yellow and unlock them, and
3. the Computed cells as (say) light red and lock them

23479


You could add a 'Formats' sheet to defines what type each cell in the row is for each type of input

You could also copy the appropriate information to the Summary

You might consider simplifying the format of your "database" sheets