On 1/8/2018 at 2:10:26 PM GS wrote:

On 1/6/2018 at 7:56:51 AM GS wrote:

Oops.., didn't fully cleanup after testing! The working code
follows...

Sub RoundCurrencyValues()
Dim rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In ActiveSheet.UsedRange.Cells
If crng.NumberFormat = sCurrencyFormat Then crng.Value =
WorksheetFunction.Round(crng * (1 + rng), 2) Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub

Thanks I will try the code out. I'm having problems understanding
how to declare "pctChange" with local scope. I've never created a
macro myself and cannot find an example of such declaration with
Google.

The best example that I could find about scope declaration is this:
<http://www.cpearson.com/excel/scope.aspx
but it does not seem to address the local scope...

On the Formulas tab, use *Name Manager* and set the scope to the
%cell on the sheet that's active. Activate the next sheet and select
its %cell and repeat.

Garry:

Your macro seems to work fine. The only problem that I've found is
that it puts a "$0.00" in every blank cell that is formatted as
currency. If a cell is blank (and is formatted as currency), I'd like
it to stay so.

Below is the complete macro as you created it for me. Would you mind
seeing if it can be modified as per the above requirement? Also why is
there a "$" character in constant "sCurrencyFormat$"?

*****
Option Explicit

Const sCurrencyFormat$ = "$#,##0.00_);($#,##0.00)"

Sub RoundCurrencyValues()
Dim rng As Range, crng
On Error Resume Next 'in case no Range("pctChange")
Set rng = ActiveSheet.Range("pctChange")
If Not rng Is Nothing Then
For Each crng In ActiveSheet.UsedRange.Cells
If crng.NumberFormat = sCurrencyFormat Then crng.Value =
WorksheetFunction.Round(crng * (1 + rng), 2)
Next 'crng
End If 'Not rng Is Nothing
Set rng = Nothing
End Sub
*****

Thanks.
--
tb