Slow loop processing

[nyconfidential]

I was asked to find and replace pound signs with a percentage sign in a particular workbook. Rather than doing find and replace all, I want to loop through all worksheets in VBA and see if the first character in each cell is a pound sign, remove that pound sign and replace it with a percentage sign(I just want to replace a pound sign if it is the FIRST character in the cell string).

The code below accomplishes my goal, but there are 65 worksheets in this workbook, and some of the worksheets have pound signs in over 5000 cells, so it takes almost 10 minutes to complete. Can anyone tell me if there is a way to optimize my code below? Is looping through each cell in the range the smartest way to do this?(Apologies, I come from an Access background and am accustomed to looping through recordsets when updating values). Appreciate any input.

Public Sub ReplaceWorksheetValues()
On Error Resume Next

Dim WrkSht As Worksheet
Dim rCell As Range
Dim rRng As Range


For Each WrkSht In ActiveWorkbook.Worksheets
    Range("A1").Select
    Application.StatusBar = "Updating " & WrkSht.Name & ", please wait..."
    Set rRng = WrkSht.UsedRange
    For Each rCell In rRng.Cells
        'Debug.Print rCell.Address, rCell.Value
        'Find cell values that start with a pound sign
        If Left(rCell.Value, 1) = "#" Then
            'Strip the first character(pound sign), prepend an equals sign to the start of the string
            rCell.Value = "%" & Right(rCell.Value, Len(rCell.Value) - 1)
        End If
    Next rCell
    
    Application.StatusBar = "Updating " & Str(WrkSht.Name)
    
Next WrkSht
    
    
End Sub

 

[Fluff]

You're welcome