Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditionally formatting currency
In Excel 2003 Pro, is there any way of formatting currency in number format
conditionally - and how is it done? For example, cell A1 can be « $ », « ‚¬ », or « £ ». If cell A1 is « $ », then the contents of cell B1 should be formatted with a $ sign whereas if A1 = « ‚¬ », then the contents of cell B1 should be formatted with the ‚¬ sign and so on. Thanks for any help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditionally formatting currency
Yendorian,
You can use the worksheet's change event. Copy the code below, right-click the sheet tab, select "View Code", and paste the code into the window that appears. This assusmes that your cell A1 doesn't have the << and but just the currency symbols. Otherwise, you would need to clean up the value from cell A1. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$1" Then Exit Sub Range("B1").NumberFormat = Target.Value & "0.00" End Sub "Yendorian" wrote in message ... In Excel 2003 Pro, is there any way of formatting currency in number format conditionally - and how is it done? For example, cell A1 can be « $ », « ? », or « £ ». If cell A1 is « $ », then the contents of cell B1 should be formatted with a $ sign whereas if A1 = « ? », then the contents of cell B1 should be formatted with the ? sign and so on. Thanks for any help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditionally formatting currency
Thanks for that. It works perfectly with the £ and ‚¬ sign but returns the ‚¬
sign again when it should be the $. Could this be because my computer is set up with ‚¬ as the main currency? "Bernie Deitrick" wrote: Yendorian, You can use the worksheet's change event. Copy the code below, right-click the sheet tab, select "View Code", and paste the code into the window that appears. This assusmes that your cell A1 doesn't have the << and but just the currency symbols. Otherwise, you would need to clean up the value from cell A1. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$1" Then Exit Sub Range("B1").NumberFormat = Target.Value & "0.00" End Sub "Yendorian" wrote in message ... In Excel 2003 Pro, is there any way of formatting currency in number format conditionally - and how is it done? For example, cell A1 can be « $ », « ? », or « £ ». If cell A1 is « $ », then the contents of cell B1 should be formatted with a $ sign whereas if A1 = « ? », then the contents of cell B1 should be formatted with the ? sign and so on. Thanks for any help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditionally formatting currency
Try finding a currency format that works for dollars, then record a macro to
get that string, and use it in the change event like: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$1" Then Exit Sub If Target.Value = "$" Then Range("B1").NumberFormat = FORMAT STRING THAT WORKED HERE Else Range("B1").NumberFormat = Target.Value & "0.00" End IF End Sub "Yendorian" wrote in message ... Thanks for that. It works perfectly with the £ and ? sign but returns the ? sign again when it should be the $. Could this be because my computer is set up with ? as the main currency? "Bernie Deitrick" wrote: Yendorian, You can use the worksheet's change event. Copy the code below, right-click the sheet tab, select "View Code", and paste the code into the window that appears. This assusmes that your cell A1 doesn't have the << and but just the currency symbols. Otherwise, you would need to clean up the value from cell A1. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$A$1" Then Exit Sub Range("B1").NumberFormat = Target.Value & "0.00" End Sub "Yendorian" wrote in message ... In Excel 2003 Pro, is there any way of formatting currency in number format conditionally - and how is it done? For example, cell A1 can be « $ », « ? », or « £ ». If cell A1 is « $ », then the contents of cell B1 should be formatted with a $ sign whereas if A1 = « ? », then the contents of cell B1 should be formatted with the ? sign and so on. Thanks for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditionally Formatting | Excel Worksheet Functions | |||
Conditionally Formatting/Code Only One Word in a Formula | Excel Worksheet Functions | |||
Conditionally formatting rows | Excel Worksheet Functions | |||
Conditionally formatting highest valued cell? | Excel Worksheet Functions | |||
Conditionally formatting other cells? | Excel Worksheet Functions |