Conditional formatting to every cell in a column

[Guest]

Hi

I am using Excel 2003. I have a column in which I am entering text. I want
to make sure that I do not enter more than 50 characters. I was hoping I
could do this with conditional formatting by changing the background color of
a cell if its text exceeds the maximum length.

I am able to do this once cell at a time by using the following formulas as
my formatting criteria:

Len(C1)>50
Len(C2)>50
Len(C3)>50
.....
Len(Cn)>50

Is there a way that rather than entering these formulas one cell at a time,
that I can enter it once for the column as a whole so that for every cell in
that column, my conditional formatting will be applied if the length of the
text in that particular cell exceeds the maximum length?

Any help will be greatly appreciated.

Thanks,
Paul

 

[PCLIVE]

Select the column. Go to Conditional Formatting. Select 'Formula Is'.
Use this formula and then set your Format throught the Format button. This
example uses column C.

=LEN(C1)>50

HTH,
Paul

 

[David Biddulph]

If you've got the CF set up in C1, select that cell, click on the format
painter button, then select the column.

Alternatively, select C1, copy, select the column, and Edit/ Paste Special/
Format.

 

[Guest]

use one of the other two methods, but be sure that the formula is does not
have absolute cell referencing "$". I occassionally get an absolute
reference when I want a relative one and visa versa and have never figured
out why.

 

[Stan Brown]

Fri, 8 Jun 2007 16:04:49 +0100 from <"David Biddulph" <groups [at]
biddulph.org.uk>>:

If you've got the CF set up in C1, select that cell, click on the format
painter button, then select the column.

By "the column" do you mean the column letter and not a range of
cells?

I conditional-formatted cell X4, then clicked on it and Format
Painter, then selected X5 through X8. The conditional formatting was
copied *but* the formula in all the cells referred to X4.

These are quiz scores. I want to format in color if the score is more
than 20% better than the previous score. So in conditional formatting
cell X4 I use the formula
=and(X4>W4*1.2,W4>0)
and the format is green text. But as I say, cells X5 through X8 get
he identical formula, with the relative reference not updated.

 

[David Biddulph]

Are you sure that you've got relative addressing and not absolute
addressing? If the formula in all the rows referred to X4 when you format
painted it down, then I expect that your formula referred to X$4 or $X$4,
rather than to X4.
--
David Biddulph

Fri, 8 Jun 2007 16:04:49 +0100 from <"David Biddulph" <groups [at]
biddulph.org.uk>>:

If you've got the CF set up in C1, select that cell, click on the format
painter button, then select the column.

By "the column" do you mean the column letter and not a range of
cells?

I conditional-formatted cell X4, then clicked on it and Format
Painter, then selected X5 through X8. The conditional formatting was
copied *but* the formula in all the cells referred to X4.

These are quiz scores. I want to format in color if the score is more
than 20% better than the previous score. So in conditional formatting
cell X4 I use the formula
=and(X4>W4*1.2,W4>0)
and the format is green text. But as I say, cells X5 through X8 get
he identical formula, with the relative reference not updated.

 

[Stan Brown]

Sat, 9 Jun 2007 08:57:57 +0100 from <"David Biddulph" <groups [at]
biddulph.org.uk>>:

Are you sure that you've got relative addressing and not absolute
addressing? If the formula in all the rows referred to X4 when you format
painted it down, then I expect that your formula referred to X$4 or $X$4,
rather than to X4.

Yes, I'm very sure it was X4 and had no dollar signs. I would have
expected the cell references to update as they do when copying
regular formulas, but they didn't.

 

[Stan Brown]

Sat, 9 Jun 2007 13:06:42 -0400 from Stan Brown

Sat, 9 Jun 2007 08:57:57 +0100 from <"David Biddulph" <groups [at]
biddulph.org.uk>>:

Are you sure that you've got relative addressing and not absolute
addressing? If the formula in all the rows referred to X4 when you format
painted it down, then I expect that your formula referred to X$4 or $X$4,
rather than to X4.

Yes, I'm very sure it was X4 and had no dollar signs. I would have
expected the cell references to update as they do when copying
regular formulas, but they didn't.

Mystery solved!

Here's what I think the problem was. I no longer have the test
worksheet, but I *think* I typed the conditional-formatting formula
as
x4>1.2*w4
instead of
=x4>1.2*w4

I didn't notice it in my original test, but just now when I tried it
I see that
x4>1.2*w4
gets converted to
="x4>1.2*w4"
which of course will not change when copied to another cell.

I've verified that
=x4>1.2*w4
*does* change appropriately when formatting is copied to another
cell, just as David said (and as I would have expected).