automatically changing the format of a cell?

[scottwilsonx]

Does anyone know if there is a way to change the format of a cell
depending upon what is entered into another cell ?

Example:
In my spreadsheet, cell B15 contains a drop down list of 3 values "A"
"B" or "C".
Depending upon what is in that cell, the value in B19 changes.

However, when the value of B15 is "A" or "B". then I want B19 to sho
no decimal places, when B15 contains "C" I want B19 to display
decimal places.

Is this possible using a function or vba ?

Many thanks for your help.

Regards

Scott

 

[JE McGimpsey]

One way:

B19: =IF(OR(B15="A",B15="B"),TEXT(<your formula>,"0"),TEXT(<your
formula>,"0.00"))

which leaves the values as text.


You can also use a Event macro. Put this in your worksheet code module
(right-click the worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) <> "B15" Then Exit Sub
If .Value = "A" Or .Value = "B" Then
Range("B19").NumberFormat = "0"
Else
Range("B19").NumberFormat = "0.00"
End If
End With
End Sub