Expert: Jan Altman - 5/11/2005

Question
I want to change a cell color based on the length of a fiels.  I used =LENGTH(A1) to get the number of charaters.

If the lenght is more than 40, I want it to change to red.  How do I do that in Excel?
Thanks Chris
512-507-0260

Answer
Hi, Chris,

You need a feature called conditional formatting. It's powerful and very easy to do.

Conditional formatting allows us to change formatting based upon cell values or formulas. Follow these steps to conditionally format A1:

1) Click in A1.
2) On the menu bar, select Format/Conditional Formatting.
3) Drop-down Condition 1 and choose Formula Is.
4) In the long field to the right, enter the following formula:

=LEN(A1)>40

We're testing for when the length of A1 is greater than 40. Now we'll set a format to be applied when the test is true.

5) Click the Format button.
6) In the Format Cells box, choose red under Color. (You might also consider adding bold to make the color more visible.)
7) Click OK to return to the Conditional Formatting box.
8) Click OK to apply the conditional format.

When the length of A1 is 40 or below, it will retain the default formatting (or whatever you've applied directly). When it becomes more than 40, it will turn red (or whatever you specified in the Conditional Formatting box).

By the way, if you want to apply conditional formatting to a range of cells, simply choose the entire range first. When you specify the formula, refer to only the cell that's active. The whole range will be formatted.

I think you'll enjoy using conditional formatting, Chris. It's really quite a powerful feature.


Cheers,
Jan