Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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/ |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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/ |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column conditional formatting | Excel Discussion (Misc queries) | |||
Conditional formatting when cell value is top 3 of column . . . | Excel Discussion (Misc queries) | |||
BULK Conditional Formatting - by column without going into each cell? | Excel Discussion (Misc queries) | |||
column to column conditional formatting won't work, need formula | Excel Worksheet Functions | |||
Conditional formatting row if cell value in certain column is even | Excel Worksheet Functions |