Hi, Y'all,

New to the forum and kind of a novice. I have about 70-some-odd workbooks with a column that has a RouteID like so:

BLU1
BLU2
...
BLU39
GRE1
GRE2
...
GRE40
ORA1
...etc

Need to renumber to BLU01, BLU02, GRE01, GRE02, etc, cuz as y'all know, when you sort with numbers like I have, it comes out BLU1, BLU11, BLU12, etc. I've tried the following, to no avail, as you can see I've tried nested and un-, and pound signs and stars as the wildcards. It works to convert the first series (BLU), but won't cycle past that to select the next route in the series. Some of these sheets have up to 12 Route codes with as many as 38-43 ID's per Route, and they vary, hence the need for Wilds. Any ideas? I am also posting a sample workbook with the code attached if ya wanna play around with it. Thanks in advnce for any help!

Sub RenumberRouteID()
' Sub RenumberRouteID
' Renumbers to "01" etc. for sorting

'Declare Variables
Dim Route
Dim RouteID As String
Dim ColorCount
Dim RowCount
ColorCount = 0
RowCount = 0

'Name Column A Range
    Sheets("CONSOL").Select
    Range("A4:A2000").Select
    ActiveWorkbook.Names.Add Name:="CONSRouteID", RefersToR1C1:="=CONSOL!R4C1:R2000C1"
    'Activate first Cell in Range
    Range("A4").Select
        
    For Each Route In Range("CONSRouteID").Cells
        'If Route.FormulaR1C1 = "RouteID" Like "BLU##" Then
            If Route.FormulaR1C1 = "BLU1" Then
                ActiveCell.FormulaR1C1 = "BLU01"
                Selection.AutoFill Destination:=Range(Cells(4, 1), Cells(4 + 8, 1)), Type:=xlFillDefault
            End If
            'RowCount = RowCount + 1
            'Range(Cells(4 + RowCount, 1)).Select
        'End If
    Next
    For Each Route In Range("CONSRouteID").Cells
        If Route.Value = "BLU*" Then
            RowCount = RowCount + 1
            Range(Cells(4 + RowCount, 1)).Select
        End If
    Next
End Sub
200809_MONTHLY_HELP.xlsm