Why can't Conditional Formatting do other formating such as number

I want to set the number of decimal places for a cell based on a condition in
another cell. You cna't do that in conditional formatting. Is there another
way?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...heet.functions

Gene,

To show the number from cell A1 in another cell with as conditional number of decimal places, use a
formula like

=TEXT(A1,"0." & REPT("0",some conditional formula that returns a number)

like:

=TEXT(A1,"0."&REPT("0",IF(A1>100,2,3)))

But then it isn't a number - some functions will treat it as a number ( using +, for example) while
others (SUM()) will not...

HTH,
Bernie
MS Excel MVP


"Gene Herron" <Gene [email protected]> wrote in message
news:[email protected]...
>I want to set the number of decimal places for a cell based on a condition in
> another cell. You cna't do that in conditional formatting. Is there another
> way?
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...heet.functions

Thank you for your reply, but what I'm trying to do is change the formatting
of the cell if a condition is met in another cell. So that if 37 is entered
in the cell if the condition is met it displays 37.0, but if it is not met it
displays 37. If you write a formula in a cell, when you enter 37 in the cell
the formula is erased.

Gene

"Bernie Deitrick" wrote:

> Gene,
>
> To show the number from cell A1 in another cell with as conditional number of decimal places, use a
> formula like
>
> =TEXT(A1,"0." & REPT("0",some conditional formula that returns a number)
>
> like:
>
> =TEXT(A1,"0."&REPT("0",IF(A1>100,2,3)))
>
> But then it isn't a number - some functions will treat it as a number ( using +, for example) while
> others (SUM()) will not...
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Gene Herron" <Gene [email protected]> wrote in message
> news:[email protected]...
> >I want to set the number of decimal places for a cell based on a condition in
> > another cell. You cna't do that in conditional formatting. Is there another
> > way?
> >
> > ----------------
> > This post is a suggestion for Microsoft, and Microsoft responds to the
> > suggestions with the most votes. To vote for this suggestion, click the "I
> > Agree" button in the message pane. If you do not see the button, follow this
> > link to open the suggestion in the Microsoft Web-based Newsreader and then
> > click "I Agree" in the message pane.
> >
> > http://www.microsoft.com/office/comm...heet.functions
>
>
>

Gene,

You could use an event: copy this code, right-click the sheet tab, select "View Code" and paste the
code into the window that appears. This example will format cell B1 with the number of decimals
that is given in cell D3. This would also require that the sheet have some formulas that would
force the calculate event.

Private Sub Worksheet_Calculate()
Range("B1").NumberFormat = "0." & Application.Rept("0", Range("D3").Value)
End Sub

HTH,
Bernie
MS Excel MVP


"Gene Herron" <[email protected]> wrote in message
news:[email protected]...
> Thank you for your reply, but what I'm trying to do is change the formatting
> of the cell if a condition is met in another cell. So that if 37 is entered
> in the cell if the condition is met it displays 37.0, but if it is not met it
> displays 37. If you write a formula in a cell, when you enter 37 in the cell
> the formula is erased.
>
> Gene
>
> "Bernie Deitrick" wrote:
>
>> Gene,
>>
>> To show the number from cell A1 in another cell with as conditional number of decimal places, use
>> a
>> formula like
>>
>> =TEXT(A1,"0." & REPT("0",some conditional formula that returns a number)
>>
>> like:
>>
>> =TEXT(A1,"0."&REPT("0",IF(A1>100,2,3)))
>>
>> But then it isn't a number - some functions will treat it as a number ( using +, for example)
>> while
>> others (SUM()) will not...
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Gene Herron" <Gene [email protected]> wrote in message
>> news:[email protected]...
>> >I want to set the number of decimal places for a cell based on a condition in
>> > another cell. You cna't do that in conditional formatting. Is there another
>> > way?
>> >
>> > ----------------
>> > This post is a suggestion for Microsoft, and Microsoft responds to the
>> > suggestions with the most votes. To vote for this suggestion, click the "I
>> > Agree" button in the message pane. If you do not see the button, follow this
>> > link to open the suggestion in the Microsoft Web-based Newsreader and then
>> > click "I Agree" in the message pane.
>> >
>> > http://www.microsoft.com/office/comm...heet.functions
>>
>>
>>