Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Can I conditionally format a cell based on whether it is pro

  1. #1
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'd like to format my workbooks so that the font color of entries to unprotected cells is blue and the font color to protected cells is black, similar to 1-2-3. Any way to do this? Thanx in advanx.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When you go into 'FormatCells...' to unlock your selected cells, go into the FONT tab at the same time and select blue.

  3. #3
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you wish to do this often, you might consider creating a cell "Style" for protected cells. Then just apply that "style" on selected cells. You can also apply "style" in VBA coding based on cell value,range,etc. Just a few seconds time saver.

  4. #4
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanx, Big Blue & lenze, but I would like this to automatically change the font color whenever I protect or unprotect any cell(s), similar to what Lotus does. Doesn't look possible, does it?

  5. #5
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, but if you set up a style for unprotected cells, you simply change the style to protect and unprotect cells. Example:

    Go to Format>Styles. You will see a name box displaying the style name of the active cell, usually normal. Type in a new name, such as 'protected' and click add. Then choose modify and select font color blue and unlock for protection. Now you can apply this style to cells you wish to unprotect and apply Normal to cells you wish to remain locked. You will no longer use the Format dialog to lock and unlock cells. Just toggle between the styles

    [ This Message was edited by: lenze on 2002-04-24 07:43 ]

  6. #6
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanx, lenze. I'll try it right now.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Barry,

    Globally format your font colour to black

    then choose conditional format and try :

    =CELL("protect",a1)=0 with the associated font format as your blue

    you should find your (default) locked cells are black font, with any unlocked cells blue because of the conditional formatting kicking in



    _________________
    Hope this helps,
    Chris



    edit - apply conditional format with all sheets selected and all cells highlighted

    [ This Message was edited by: Chris Davison on 2002-04-24 14:42 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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