Change decimal format of cells depending on conditions?

[Guest]

Hi all. I'm looking to change the format of cells to two-decimals when they
represent price per pound, but switch to comma-format when otherwise. This
is because the sheet shows lbs, dollars or price depending on the switch
selected, or combinations of them in different areas, and the lbs and dollars
columes are in the millions (decimals are confusing to the audience in this
case).

Thanks
ccc

 

[Ron Rosenfeld]

Hi all. I'm looking to change the format of cells to two-decimals when they
represent price per pound, but switch to comma-format when otherwise. This
is because the sheet shows lbs, dollars or price depending on the switch
selected, or combinations of them in different areas, and the lbs and dollars
columes are in the millions (decimals are confusing to the audience in this
case).

Thanks
ccc

You can do that by using a VBA event triggered macro (Worksheet_Change, for
example).

Here is an example assuming that your values are in A1:A10 and your "switch" is
in the adjacent column B1:B10. You will obviously have to modify this to suit
your needs.

=========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Range, switch As Range

Set v = Range("A1:A10")
Set switch = Range("B1:B10")

If Not Intersect(Target, v) Is Nothing Then
Select Case Target.Offset(0, 1).Value
Case Is = "$/lb"
Target.NumberFormat = "#.00"
Case Is = "$", "lb"
Target.NumberFormat = "#,#"
Case Else
Target.NumberFormat = "General"
End Select
End If

If Not Intersect(Target, switch) Is Nothing Then
Select Case Target.Value
Case Is = "$/lb"
Target.Offset(0, -1).NumberFormat = "#.00"
Case Is = "$", "lb"
Target.Offset(0, -1).NumberFormat = "#,#"
Case Else
Target.Offset(0, -1).NumberFormat = "General"
End Select
End If


End Sub
=====================


--ron