Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Thread: Conditional formating

  1. #11
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Matti:

    Concerning the re-calculation of cells:

    Once the formulas are in place just press "F9" this will cause all formulas to recalculate.

  2. #12
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Matti:
    RE: conditional format base on LOCKED cell

    If you are going to have the formula cells locked then there is another way to change the text color without a macro.
    You can do a "conditional format" based on if a cell is locked or not !

    1-Go into Format ... conditional format..
    2-choose "formula is" from drop down
    3-type in this formual : =CELL("protect")=0
    4-make your format choices
    5-select add button
    6-type in this formula: =CELL("protect")=1
    7-make your format choices

    Done : now you have a special format for lock=1, and unlocked = 0

  3. #13
    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 agree with Nimrod that locking cells with formulas is the way to go - prevents users from overwriting them. I also think Brian from Maui has the right idea - changing the format of unprotected cells so that the user knows which cells he/she/it may enter to.

    Chris Davison just helped me last week with some code to emulate Lotus' display of unpretected cells with blue font and protected with black. You run the code whenever you open a new workbook and it goes to the first, second and third sheet of the workbook, locks all the cells in each and, when you un-lock any cells, converts the font in those cells to blue. This code could be modified to show all unproted cells with, say, light yellow background (light yellow won't show on a printout), even more helpful to users, since the blue font only shows after you enter something to the un-locked cells.

    Let me know if you want this code and I'll paste it onto a reply.

    _________________
    ...and I always put the seat down.

    [ This Message was edited by: Barry Katcher on 2002-05-08 14:16 ]

  4. #14
    New Member
    Join Date
    May 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Guys,

    I'm more than satisfied with your answers and many tks again.

    Another challenge though - in this same workbook I would like to have a pop up box or some sort of animation to tell the user that as a result of his input certain cell/cells has/have invalid value. This animation should remain until cells content is rectified. The animation should be visible in any spreadsheet within this workbook. Am I asking too much?

  5. #15
    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

    Don't know about hula dancers and dancing elephants, but why not use Data/Validation. This will prevent any invalid data from being entered in the first place, and you can use both a prompt box and an invalid data entry box to alert the users of their boo-boo.
    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


  6. #16
    New Member
    Join Date
    May 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Tks Barry,

    I'm well aware of the data validation function however it won't do. I'll give an example - in this spread sheet I have among the rest a balance sheet. I need to alert the user if the balance sheet doesn't balance. Now that can happen for a variety of reasons that can not be defined in data validation. I could happen as a result of a row being deleted by mistake in some other spread sheet with in the same work book.

    Do you see what I mean? It will have to be a VBA code of some sort.

    Tks again,

    Matti.

  7. #17
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Matti:
    First of all you may want to use cell protection for such things as rows can't be deleted .
    Second... please give me a specific example of what you would test for that would show the Balance sheet does not balance. If you give me that I will try to help.

    P.S. When you have a new problem you may want to start a new Thread of discussion to increase your responses.

  8. #18
    New Member
    Join Date
    May 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Nimrod,

    It's quite straightforward - As the workbook contains many spread sheets I've created check cells (the balance sheet check cells for example will get a value <> to zero or #ref# if something goes wrong). Now like I mentioned things can go wrong due to structural changes made in other parts of the spread sheet and I have to highlight the fact that the check cells indicate that something has gone wrong. So roughly speaking an if statement will be - if cell x <> 0 or if #ref# appears then give me a pop up box or something that will flesh so long that this problem is not rectified.

    I canít lock anything as yet as this file is still dynamic and changes are made to it on a daily basis.

    I hope Iím clear but let me know if thereís anything you still donít understand.

    Many tks,

  9. #19
    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

    This is how I do it on my spreadsheets: Whenever I crossfoot a spreadsheet, for the grand total at the bottom right I put in the formula:

    =IF(sum(F1:F100)=sum(A101:E101),sum(F1:F100),"Error, you fool")

    If I made an error somewhere, forgot a formula, etc., and the sum of the last column doesn't match the sum of the bottom row, I get the "Error, you fool" instead of the correct total. If you wrap text in that cell, a long message will simulate a drop-down box.

    This doesn't tell me what the error is but it does alert me that there is one so I can investigate. If this is sufficient, you can alter the sum formulas in the first part to whatever suit your needs.

    _________________
    ...and I always put the seat down.

    [ This Message was edited by: Barry Katcher on 2002-05-09 12:24 ]

  10. #20
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you wrap text in that cell, a long message will simulate a drop-down box.

    That is neat Barry Katcher!


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
  •