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
--
HTH...

Jim Thomlinson


"RM270" wrote:

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.

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" wrote:

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
--
HTH...

Jim Thomlinson


"RM270" wrote:

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.

=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.
--
HTH...

Jim Thomlinson


"RM270" wrote:

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" wrote:

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
--
HTH...

Jim Thomlinson


"RM270" wrote:

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.

Do it in one condition.

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


Gord Dibben MS Excel MVP

On Fri, 19 Feb 2010 08:15:01 -0800, RM270
wrote:

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" wrote:

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
--
HTH...

Jim Thomlinson


"RM270" wrote:

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.

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

"Jim Thomlinson" wrote:

=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.
--
HTH...

Jim Thomlinson


"RM270" wrote:

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" wrote:

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
--
HTH...

Jim Thomlinson


"RM270" wrote:

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.