Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting (2002)

    Conditional formatting: Formula is: =IF(OR(LEN(E15<>0)),(LEN(E15<>6)))

    Format if the cell is not blank or contains 6 characters ??

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

    Re: Conditional Formatting (2002)

    The parentheses are placed incorrectly, and the logic makes no sense. I think you want this - if not, try to explain more clearly what you want to accomplish.

    =AND(LEN(E15)<>0,LEN(E15)<>6)

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2002)

    Yes Thanks, that's what I was looking for.

    Another one.

    =AND(LEN(E5)<>0,LEN(E5)<>16,LEN(E5)<>17)

    I want the format if the length is not 0, 16 or 17 but if 17, it should contain 16 digits and 1 space, not 17 digits. Is that possible?

    Also, when I lock my worksheet, the cell contents do not appear in the formula bar, which stops the user being able to edit an unlocked cell. Is there a way around this?

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

    Re: Conditional Formatting (2002)

    Are you talking about data validation or about conditional formatting?

    If you protect a sheet, the cell values/formulas should still be displayed in the formula bar unless you have ticked the Hidden property of the cells in the Protection tab of Format | Cells...

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2002)

    Conditional formatting. I want it to be formatted on 17, only if the 17 does not contain a space. If possible?

    5555555555555555 No format
    555555 5555555555 No Format
    55555555555555555 FORMAT

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

    Re: Conditional Formatting (2002)

    Does this do what you want?

    =AND(LEN(E5)<>0,LEN(E5)<>16,IF(LEN(E5)=17,AND(ISNU MBER(1*LEFT(E5,16)),RIGHT(E5,1)=" "),TRUE))

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2002)

    nearly, it works on 16, works on 16 plus a space, but fail's with 17.

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

    Re: Conditional Formatting (2002)

    This?

    =AND(LEN(E5)<>0,LEN(E5)<>16,IF(LEN(E5)=17,NOT(AND( ISNUMBER(1*LEFT(E5,16)),RIGHT(E5,1)=" ")),TRUE))

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2002)

    nearly, it works on 16, fails on 16 plus a space, and works with 17. The space would always be the 7th character of 17 (if that helps)

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

    Re: Conditional Formatting (2002)

    Last attempt:

    =AND(LEN(E5)<>0,LEN(E5)<>16,IF(LEN(E5)=17,MID(E5,7 ,1)<>" ",TRUE))

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting (2002)

    And a good one!! Thanks

Posting Permissions

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