Help with Conditional Formatting

  • Thread starter Thread starter RM270
  • Start date Start date
R

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.
 
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
 
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?
 
=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.
 
Thank you! It does just what I want it to do! And thanks for explaining how
it works. That helps a whole lot.
 
Back
Top