I am trying to write a macro to copy the format in the current cell and apply
it to all cells in the book that are currently formated as a currency. Any
idea of why the below is not working?

Sub CurrencyCopy()

Dim rng As Range
Dim format As String
format = ActiveCell.NumberFormat
Msg = "This will reformat all cells currently containing a currency
format. Are you sure you wish to proceed?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then
Set rng = ActiveSheet.UsedRange
For Each cell In rng
If VarType(cell) = vbCurrency Then
ActiveCell.NumberFormat = format
End If
Next cell
End If

'
End Sub



Thanks for the help!

I looked up VarType in help and it looks like it applies to variables, not
cells. You can probably try this instead:

Instead of:
If VarType(cell) = vbCurrency Then

Do this:
If cell.NumberFormat = "$#,##0.00" Then

I got "$#,##0.00" by formatting a cell to "Currency" and then selecting the
cell and doing a ?activecell.numberformat in the immediate pane to see how
"Currency" is formatted.


"Mike Piazza" wrote:

I am trying to write a macro to copy the format in the current cell and apply
it to all cells in the book that are currently formated as a currency. Any
idea of why the below is not working?

Sub CurrencyCopy()

Dim rng As Range
Dim format As String
format = ActiveCell.NumberFormat
Msg = "This will reformat all cells currently containing a currency
format. Are you sure you wish to proceed?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then
Set rng = ActiveSheet.UsedRange
For Each cell In rng
If VarType(cell) = vbCurrency Then
ActiveCell.NumberFormat = format
End If
Next cell
End If

'
End Sub



Thanks for the help!