Using value in a cell as part of formating ?

SHEETMAGIC

Board Regular
Joined
May 3, 2005
Messages
209
Hello,
I would like to know if there is anyway to include the value in a cell in my CUSTOM formating.

What I have is a cell that show a currency value. I need it to be in the standard format of " $ 5.00 " yet I need the $ to show the correct currency symbol as specifid in another cell. So let's say cell J15 has the value in it, like "US$" or the euro symbol, etc. I need that value to be used as the currency symbol in my cell with "$ 5.00".


Can this be done?

With thanks,

Jonathan
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, Jonathan,

without ever having done this, it's not so difficult to find a solution
record a macro changing the symbol
then replace "$" with " & CS & "
where CS is the symbol, retreived from a cell (A1)
in fact it's just playing a bit with some strings
Code:
Sub change_currencysymbol()
'Erik Van Geit
'051118 0035
Dim CS As String    'currency symbol

CS = Range("A1")
Range("data").NumberFormat = _
"_-[$" & CS & "-409]* #,##0.00_ ;_-[$" & CS & "-409]* -#,##0.00 ;_-[$" & CS & "-409]* ""-""??_ ;_-@_ "

End Sub
kind regards,
Erik
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
couldn't resist :)
using this your format will be changed automatically
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'change_currencysymbol
'Erik Van Geit
'051118 0035
Dim CS As String    'currency symbol
Dim CurrencyCell As Range

Set CurrencyCell = Range("A1")

If Target <> CurrencyCell Then Exit Sub

CS = CurrencyCell
Range("data").NumberFormat = _
"_-[$" & CS & "-409]* #,##0.00_ ;_-[$" & CS & "-409]* -#,##0.00 ;_-[$" & CS & "-409]* ""-""??_ ;_-@_ "

End Sub
 

SHEETMAGIC

Board Regular
Joined
May 3, 2005
Messages
209
Oh Eric.
How often have you come through not only with the basic answer but then with a super-duper solution that goes beyond what I had imagined getting.

I will test this out shortly and let you know the result.

Many many thanks. I greatly appreciate your great assistance and generous attitude.

Blessings,

Jonathan
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

You're welcome Jonathan,
(one of my sons has same name :) )


this might be not clear:
then replace "$" with " & CS & "
in fact it has to be
then replace $ (no quotes) with " & CS & " (quotes included !)

playing with strings is a thing we have to do carefully

Range("A1:A25") = 123

C = 1
Range("A" & C & ":A25") = 123

best regards,
Erik
 

SHEETMAGIC

Board Regular
Joined
May 3, 2005
Messages
209
Hi Eric,
I have at long last sat down to look into this and apply it to my worksheet.

I think perhaps I should explain my situation a little better, as this doesn't quite fit.

What I have is
Code:
A          B               C             D          E
ID        Ord Cur       Prod $     Ship $ ....
----------------------------------------	
1         US$	         78.00       44
2         €	           104.85     44
3         US$	         624.00     33

Off to the right are three other currency value fields.
What I'd like is to be able to get any one of them to show the correct currency identifier (US$, €, etc) as set in B on that same row.

Is that possible or too difficult?

I will use the code you have given me so far on my other INVOICE sheet, as I only have one instance of everything there (i.e. one currency type set, and one currency value that needs to use it). Whereas on the ORDERS sheet I have an instance on every row.

Blessings,

Jonathan
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Jonathan,

there is no "numberformat-change-event"
so this code will fail if there is no real change in the cells
edit the range: Range(Cells(cell.Row, "D"), Cells(cell.Row, "F")).NumberFormat = cell.NumberFormat
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RNG As Range
Dim cell As Range
Dim CurrencyRange As Range

Set CurrencyRange = Columns(2)

Set RNG = Intersect(Target, CurrencyRange)
If RNG Is Nothing Then Exit Sub

For Each cell In RNG
Range(Cells(cell.Row, "D"), Cells(cell.Row, "F")).NumberFormat = cell.NumberFormat
Next cell
End Sub
another problem is the undo is not anymore available when a change is made in column 2

therefore I would allow currency changes only using a dropdown changing the selected range-numberformat
this is nice:
Code:
Private Sub listbox1_Click()
'change_currencysymbol
'Erik Van Geit
'051118 0035
Dim CS As String    'currency symbol
Dim RNG As Range

Set RNG = Intersect(Selection, Columns(2))
    If RNG Is Nothing Then
    MsgBox "Please select range in column 2", 48, "SELECTION ERROR"
    Exit Sub
    End If
        If RNG.Areas.Count > 1 Then
        MsgBox "Please only select one area", 48, "SELECTION ERROR"
        Exit Sub
        End If

CS = listbox1
RNG.NumberFormat = _
"_-[$" & CS & "-409]* #,##0.00_ ;_-[$" & CS & "-409]* -#,##0.00 ;_-[$" & CS & "-409]* ""-""??_ ;_-@_ "
RNG.Offset(0, 2).Resize(RNG.Count, 3).NumberFormat = RNG.NumberFormat
listbox1 = ""
End Sub
edit the blue part
RNG.Offset(0, 2).Resize(RNG.Count, 3).

I hope you will enjoy this
kind regards,
Erik
 

Watch MrExcel Video

Forum statistics

Threads
1,119,128
Messages
5,576,251
Members
412,709
Latest member
Rishu
Top