Conditionally Formatting Locked and Unlocked Cells
There are times when your users will find it easier to enter data directly into worksheet cells, instead of a userform interface. You’ll want to protect all the cells containing formulas and static header labels, while allowing certain cells to be unprotected for users to input data.
Here is how you want to present your worksheet to your users, so they know where to enter data:
You can conditionally format unlocked cells with this boolean (True or False) formula:
=CELL("PROTECT",A1)=0
The above formula is what is being used in this example, shown in the following pictured steps.
As a quick aside, you can conditionally format locked cells with this True/False formula:
As a quick aside, you can conditionally format locked cells with this True/False formula:
=CELL("PROTECT",A1)=1
Although not absolutely necessary, it is advisable 99.9% of the time that you protect your worksheet after you have installed the conditional formatting. This will ensure that users will only have access to the unlocked cells.
Step 1 — Select the entire used range.
Step 2 — Press
• Go to the Protection tab.
• Select the option for Locked.
• Click OK, to make sure all cells in that range are locked.
Alt+O+E to show the Format Cells dialog box:• Go to the Protection tab.
• Select the option for Locked.
• Click OK, to make sure all cells in that range are locked.
Step 3 — Select the range(s) where you want to unlock the cells for users to enter data.
Step 4 — Press
• Go to the Protection tab.
• Deselect the option for Locked.
• Click OK.
Alt+O+E to show the Format Cells dialog box:• Go to the Protection tab.
• Deselect the option for Locked.
• Click OK.
IF YOU ARE USING EXCEL VERSION 2003 OR BEFORE:
Step 5 (version 2003 or before) — Press
• From the drop down list, select Formula Is.
• Enter the formula
• Click the Format button.
Alt+O+D to show the Conditional Formatting dialog box:• From the drop down list, select Formula Is.
• Enter the formula
=CELL("PROTECT",A1)=0• Click the Format button.
Step 6 (version 2003 or before) — In the Format Cells dialog box:
• Go to the Patterns tab.
• Select a color from the palette. I chose yellow.
• Click OK.
• Go to the Patterns tab.
• Select a color from the palette. I chose yellow.
• Click OK.
Step 7 (version 2003 or before) —
In the Conditional Formatting dialog box, click OK, and you are done.
In the Conditional Formatting dialog box, click OK, and you are done.
IF YOU ARE USING EXCEL VERSION 2007 OR AFTER:
Step 5 (version 2007 or after) —
Press
• In the “Show Formatting Rules for” field, select Current Selection.
• Click on the item labeled “New Rule”.
Press
Alt+O+D to show the Conditional Formatting Rules Manager dialog box:• In the “Show Formatting Rules for” field, select Current Selection.
• Click on the item labeled “New Rule”.
Step 6 (version 2007 or after) — In the New Formatting Rule dialog box:
• Select “Use a formula to determine which cells to format”
• Enter the formula
• Click the Format button.
• Select “Use a formula to determine which cells to format”
• Enter the formula
=CELL("PROTECT",A1)=0• Click the Format button.
Step 7 (version 2007 or after) — In the Format Cells dialog box:
• Go to the Fill tab.
• Select a color from the palette. I chose yellow.
• Click OK.
• Go to the Fill tab.
• Select a color from the palette. I chose yellow.
• Click OK.
Step 8 (version 2007 or after) —
In the Conditional Formatting Rules Manager dialog box, click OK.
In the Conditional Formatting Rules Manager dialog box, click OK.
Step 9 (version 2007 or after) —
In the New Formatting Rule dialog box, click OK, and you are done.
#evba #etipfree #kingexcelIn the New Formatting Rule dialog box, click OK, and you are done.
1000 Excel and VBA ebooks free Download on EVBA.info and EtipFree.com
500+ VBA ebooks free Download
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1
#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1
#evba #etipfree #kingexcel
Hi, I'm Mr KING Admin of KINGEXCEL.INFO .Thank you so much and I hope you will have an interesting time with KINGEXCEL.INFO. Mail: quantriexcel@gmail.com - PHONE : +84779313987.