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" <GeneHerron@discussions.microsoft.com> wrote in message
news:CF04A2AD-7A1F-4EC3-96D6-32572C0E7BAF@microsoft.com...
> 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 Herron@discussions.microsoft.com> wrote in message
>> news:3D5004E1-419F-43F5-A8FE-B4AB26FFA003@microsoft.com...
>> >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
>>
>>
>>