Locking Named Ranges

[ScottInTexas]

I have a zillion named ranges and I need to go through the list and lock them. But I am getting an error "Unable to set the locked property of the Range class." Suppose the range is named LockMe1 then I am trying to do

Range("LockMe1").Locked=true

Putting this in a loop and cycling through all the Names in the Application should allow me to lock all the ranges I am interested in rather than go through several hundred ranges.

For each n in Application.Names
      If Range(n.Name).Interior.Color=12343456 then 
          Range(n.Name).Locked=True
      End If
Next

But I get that error.

 

[dmt32]

Hi

Untested but Try

Sub LockNamedRanges()
  Dim nm As Name
  For Each nm In ThisWorkbook.Names
       With Range(nm)
          .Locked = CBool(.Interior.Color = 12343456)
        End With
  Next nm
End Sub

Dave

 

[BasN]

You can breakdown the code

Range("LockMe1").Locked=true

in two parts

Range("LockMe1").select
Selection.Locked = true

 

[Fluff]

Another option

Dim n As Name
For Each n In ActiveWorkbook.Names
   If Not n.Name Like "_xl*" Then
      If Range(n.Name).Interior.Color = 12343456 Then
          Range(n.Name).Locked = True
      End If
   End If
Next