asked on

Changing to Currency Format

EE Pros,

Ssaqibh wrote a Currency Update Macro for me. I have one change to make. The Attached Workbook works fine except the Currency formatting doesn't change when making the Currency Selection. The formatting needs to be in Currency - Column I Cell 4 and below.

That's it!

Thank you in advance.

B.
Currency-Update-and-Displayv2.xlsm

Norie

Where do you set/select the currency?

Saqib Husain

Sub CurrencyUpdate()
Dim cel As Range, cn As Range
Sheets("Currency").QueryTables(1).Refresh
Set cn = Sheets("Currency").Range("J3:R3").Find(Sheets("Customer-inputs").Range("F2").Value, , xlValues)
For Each cel In Sheets("Currency").Range("G4:G10000")
cel.Offset(, 2).Value = Sheets("Currency").Cells(cel.Row, cn.Column).Value
cel.Offset(, 2).NumberFormat = Cells(cel.Row, cn.Column).NumberFormat
If cel.Value <> "" Then
    Sheets(Replace(Split(cel.Value, "!")(0), "'", "")).Range(Split(cel.Value, "!")(1)).Value = cel.Offset(, 2).Value
    Sheets(Replace(Split(cel.Value, "!")(0), "'", "")).Range(Split(cel.Value, "!")(1)).NumberFormat = cel.Offset(, 2).NumberFormat
End If
Next cel
End Sub

Open in new window

Bright01

ASKER

Ssaq,

I changed the macro; however, now it appears it is doing a lot of additional work (see the flickering) when firing "Update".

Can you take a quick look?

B.
Currency-Update-and-Displayv3.xlsm

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bright01

ASKER

That works! Will be authoring another question for another slight change to the program.

Thanks for hanging in there with me!

B.