# Thread: Conditional Formatting (2002)

1. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Conditional Formatting (2002)

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

8. ## 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. ## 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. ## Re: Conditional Formatting (2002)

Last attempt:

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

11. ## 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
•