Code for Looping thru Ranges (2000 )

Topic

New Reply

I need to write a macro that will loop through all existing named ranges in a sheet selecting each one, formatting the range, and moving to next.
THANKS in advance for any assistance.

Reply | Quote

Replies

Try something like this:

Dim oRng As Range, oCell As Range
Dim oNam As Name
    For Each oNam In Application.Names
        Set oRng = Nothing
        On Error Resume Next
        Set oRng = oNam.RefersToRange
        On Error GoTo 0
        If Not oRng Is Nothing Then
            For Each oCell In oRng
                MsgBox oCell.Address
            Next oCell
        End If
    Next oNam

Reply | Quote

THANK YOU!

Reply | Quote

If your style is complicated, I suggest that you give it a Name, as in the foillowing example:

    ActiveWorkbook.Styles.Add Name:="Total"
    With ActiveWorkbook.Styles("Total")
        .IncludeNumber = False
        .IncludeFont = True
        .IncludeAlignment = False
        .IncludeBorder = True
        .IncludePatterns = False
        .IncludeProtection = False
        .Font.Bold = True
        .Borders(xlTop).LineStyle = xlContinuous
        .Borders(xlTop).Weight = xlThin
        .Borders(xlTop).ColorIndex = xlAutomatic
        .Borders(xlBottom).LineStyle = xlDouble
        .Borders(xlBottom).Weight = xlThick
        .Borders(xlBottom).ColorIndex = xlAutomatic
    End With

You can then – using Legare’s example – format the Ranges with the following line

    oRng.Style = "Total"

All of this should be adapted to your own circumstances.

Reply | Quote