Help with Conditional Formatting

[RM270]

I am working with a large list in Excel 2003. One column is formatted as
general. Each cell in the column has numbers in it that look like this:
1234-1234. I want to have the cell turn red if there are not nine characters
in it.

I have selected conditinal formatting for the cell. I use fomula
=if(len(B4)><9) and tell it to make the cell red. It doesn't work. Can anyone
help me figure out why?

Thanks for any help given.

 

[Jim Thomlinson]

Conditional formats want functions that return true of false. To that end you
do not need the if function. Try this formula

=len(b4)<>9

as it will return true or false

 

[RM270]

Thanks Jim! Works perfectly! But I forgot about empty cells. I don't want
them to turn a color if len=0. Should I make two conditions to cover that?
Does it matter which one come first?

 

[Jim Thomlinson]

=and(len(b4)<>9, len(b4)>0)

so if B4 has more than 1 character but not 9 characters then the formula
returns true and the colour is applied... I think that is what you wanted...

In CF's it definilty matters which order you apply the formats as formats
are applied in order and once a format has been applied it does not evaluate
any further formats. So if you had cirteria 1 =len(b4)<>9 then it would not
get to your second criteria if the len was 0 as the firrts conditoin is true.
You would have to have the formats in the other order.

 

[Gord Dibben]

Do it in one condition.

=AND(LEN(B4)<>9,NOT(ISBLANK(B4)))


Gord Dibben MS Excel MVP

 

[RM270]

Thank you! It does just what I want it to do! And thanks for explaining how
it works. That helps a whole lot.