Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Change decimal format of cells depending on conditions?
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 |
#2
|
|||
|
|||
On Thu, 24 Feb 2005 15:23:08 -0800, Cornelius
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I Need to change reference sheet for all cells on a form | Excel Discussion (Misc queries) | |||
How to get excel cells to change colors depending on value | Excel Worksheet Functions | |||
When I select "format cells", the format dialog box does not disp. | Excel Worksheet Functions | |||
Change format of cells | Excel Worksheet Functions | |||
Change format of cells | Excel Worksheet Functions |