Conditionally Formatting Locked and Unlocked Cells - Ebooks Amazon Free

    • Trang

    30 December, 2019

    Home Excel tips Conditionally Formatting Locked and Unlocked Cells

    Conditionally Formatting Locked and Unlocked Cells

    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:
    Users appreciate seeing exactly where they can — and should — 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:
    =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 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 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 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.
    Step 7 (version 2003 or before) —
    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 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 =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.
    Step 8 (version 2007 or after) —
    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 #kingexcel

    1000 Excel and VBA ebooks free Download on EVBA.info and EtipFree.com



    Please Download by this link below 📤

    500+ VBA ebooks free Download


    Please Download by this link below 📤

    📤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
    Tags
    Share This
    Author Image

    About eama

    às
    Marcadores:

    No comments:

    Post a Comment

    Subscribe to: Post Comments (Atom)

    Recent Comments