Number format based on drop-down selected?

[AllShookDown]

Is there any way to format a numeric entry in a cell based on what's selected in a drop-down box without hard-coding it as text with an IF statement in a formula? Conditional formatting won't work because the only formats allowed are font, border and pattern.

For Example, if "loss dollars" is selected the cell needs to be formatted as dollars. If "loss ratio" is selected it should be formatted as a percent.

Thanks,
Jane

[lenze]

Hi Jane:

You can do this with styles. Go to Format>Styles and create new styles with the formatting you desire(or use the built in styles). Then use the WorkSheet Change Event. If your styles are named ‘style1’ and ‘style2’ and your range of cells is named ‘test’ and your drop-down is in $D$3, then your code might be.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$3" Then
If Target.Value = "loss ratio" Then
Range("Test").Style = "style1"
Else: Range("Test").Style = "style2"

End If
End If

End Sub

Modify as needed

HTH