Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code for Looping thru Ranges (2000 )

    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.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Looping thru Ranges (2000 )

    Try something like this:

    <pre>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
    </pre>

    Legare Coleman

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Looping thru Ranges (2000 )

    If your style is complicated, I suggest that you give it a Name, as in the foillowing example:<pre> 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</pre>

    You can then - using Legare's example - format the Ranges with the following line<pre> oRng.Style = "Total"</pre>

    All of this should be adapted to your own circumstances.
    Gre

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Springdale, Arkansas
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code for Looping thru Ranges (2000 )

    THANK YOU!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •