Conditional formatting to every cell in a column
 

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


--
Paul Kraemer

Conditional formatting to every cell in a column
 

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

"Paul Kraemer" wrote in message
...
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


--
Paul Kraemer

Conditional formatting to every cell in a column
 

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.
--
David Biddulph

"Paul Kraemer" wrote in message
...
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


--
Paul Kraemer

Conditional formatting to every cell in a column
 

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.

"Paul Kraemer" wrote:

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


--
Paul Kraemer

Conditional formatting to every cell in a column
 

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(X4W4*1.2,W40)
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, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Conditional formatting to every cell in a column
 

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

"Stan Brown" wrote in message
t...
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(X4W4*1.2,W40)
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, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Conditional formatting to every cell in a column
 

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, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

Conditional formatting to every cell in a column
 

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
x41.2*w4
instead of
=x41.2*w4

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

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

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/