Re: Decimal palces (Excel 97 and >)

Wassim,

A worksheet function cannot apply any form of formatting, so if you need the format to be dynamic VBA is only way, most likely in either of teh evnts you suggested.

When you say what happens when the cell changes to 6 or 19, do you mean the number of decimal places or the value ? What would determine the number of decimal places ?. Would a Text version of the number be acceptable ?. If so I believe something might be achieved with the TEXT function.

Re: Decimal palces (Excel 97 and >)

You could use Conditional Formatting, but that is limited to three conditions. If three conditions is not enough, your only choice is going to be VBA.

BTW, your formula is probably not going to do what you are expecting if A8 does not contain a text string. If A8 contains a number, I think that the LEN is going to return the length of what is currently displayed, which may be different from what is actually in the cell.

Edited by Legare for a correction.

LEN looks like it returns the length of what was entered, not what is displayed. So, 0.1234 will have a length of 6 (not 4 like your formula looks like it is expecting). Also, if you don't put a 0 in front of the decimal, Excel will insert one.

Re: Decimal palces (Excel 97 and >)

Sorry about that. You can change the Font, Border and Pattern, but not the number format in the conditional formatting. The only way I can see to do what you are asking is to use VBA code in the Change Event.

Re: Decimal palces (Excel 97 and >)

<P ID="edit" class=small>Edited by Bat17 on 03-Apr-02 03:28.</P>Not sure that this formula is shorter or more intuative <img src=/S/smile.gif border=0 alt=smile width=15 height=15> but it seems to work.

This wont get you up to 19 decimal places though as Excel gives up with about 15 significant numbers. It does format it at the same time though.
I was not quite sure if you wanted 1.123 to end up as 1.124 or 1.1231 though!