+ Reply to Thread
Results 1 to 7 of 7

conditional formatting to lock any cell with a formula

  1. #1
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686

    conditional formatting to lock any cell with a formula

    is it possible to use conditional formatting to lock any cell containing a formula?

    Or is there another way?

    If not I can add HasFormula to a selection change procedure, but I'd like it to work outside of VBA.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Why don't you protect the sheet. You can also hide the formulas

    http://j-walk.com/ss//excel/usertips/tip060.htm

    or press F5 > special > formulas then right click and unlock cells

    then in data validation add

    =CELL("protect",A1)=0
    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    686
    I am using the sheet protection.
    And have locked every current cell with a formula.

    As I add formulas later I have to remember to protect each cell.
    I just wanted it to automatically get protected.

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Guess code it is then

    VBA Noob

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578
    You can't do it with Conditional Formatting but you can do it with Validation (although it's certainly easier to lock and unlock the spreadsheet when you need to add formulas as VBA Noob suggested.

    Here's how
    1. Select all your formulas as follows;
    .......F5, then "Special" then Formulas
    2. Open Validation from Data dropdown
    3. Choose Custom and in the formula box, type =""
    No one can overwrite your formulas without first figuring out that they need to disable your validation criteria.

    ChemistB

  6. #6
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264
    Wouldn't a user just be able to delete the formula then though?

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578
    Sorry, you need to unclick "Ignore blank" also. Then it doesn't allow you to erase the formula.

    ChemistB

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1