Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    protecting conditional formatting (2002)

    Hi,
    I've helped someone put some conditional formatting on a spreadsheet so that depending on the drop down selection in Column 15 all the entries in a given row will turn one of 3 colours. They are concerned that the users of this spreadsheet will muck up the conditional formatting. It seems they've managed to muck something up on just about every spreadsheet that has been put into general use.

    Is there a way I can protect the conditional formatting, while allowing the user to either enter data into 10 of the cells in a row and use a drop down selection in the other 5 cells.

    I tried searching this site but couldn't find anything that seems to address this issue.

    Thanks for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: protecting conditional formatting (2002)

    Partial protection:

    1) Unlock some cells
    - Select the cells the user must be able to edit.
    - Select Format | Cells...
    - Activate the Protection tab.
    - Clear the 'Locked' check box.
    - Click OK.

    2) Protect the sheet
    - Select Tools | Protection | Protect Sheet...
    - Specify a password if you want (you'll have to confirm it)
    - Make sure that the check box 'Format cells' is clear.
    - Click OK.

    The user won't be able to select Format | Conditional formatting any more. Cutting/copying and pasting will still be possible, though, and this wreaks havoc with conditional formatting.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: protecting conditional formatting (2002)

    Allegedly, the copy/pasting issues with conditional formatting are fixed in 2007, though I haven't actually got around to testing that yet.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: protecting conditional formatting (2002)

    A quick test with 2007 reveals the issues are still there.
    I created a short list of entries and set data validation in a cell to restrict entry to these specific values.
    I was still able to use 'edit-paste-special-values' to copy an 'invalid' value to 'overtype' the cell.
    The good news is that the 'circle invalid entries' feature still works!

    update: ignore the above! I thought you were talking about data validation - oops - you were talking conditional formatting.
    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •