Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Naming multi-area selection (2k)

    I have approx 250 separate dynamic "areas" on a sheet . The only commonality is that
    they all are bright green (colorindex = 4) and no other cells on the sheet have that background color.

    I want to find and name this collection of areas and lock or unlock them.

    Something like:

    <pre>Private Sub NameGreenCells ()

    Dim oCell as range
    For each oCell in range("B1: F7000")
    If oCell.Interior.ColorIndex = 4 then
    ''' Add cell address to collection
    End if
    Next

    Collection_of_cells.Name = " GreenCells"
    Range("GreenCells").Locked = True
    End Sub</pre>



    Suggestions???

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Naming multi-area selection (2k)

    Something like:

    Dim rngCell as Range, rng4 as Range
    For Each rngCell In Range("B1: F7000")
    If Not rng4 is Nothing Then
    If rngCell.Interior.ColorIndex = 4 Then Set rng4 = Union(rng4, rngCell)
    Else
    If rngCell.Interior.ColorIndex = 4 Then Set rng4 = rngCell
    End If
    Next rngCell
    rng4.Name = "GreenCells"
    Range("GreenCells").Locked = True

    unless you need it for other purposes, you can skip the naming and just use:

    rng4.Locked = True

    Also, if the green cells have something in common, such as all being formulas, you can cut the number of searched cells down using the SpecialCells Method to set an intermediate range of the cells to be read in the For Each loop. (But you said that wasn't the case.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Naming multi-area selection (2k)

    Note that the maximum number of areas one can have in a single name is 224.
    BTW: With the Name Manager you can edit the address of such long names.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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