Selecting/highlighting only unprotected cells?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Selecting/highlighting only unprotected cells?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Denver, Colorado
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Selecting/highlighting only unprotected cells?

     
    I have a large worksheet that I will publish to a group. I have multiple sheets where certain cells are protected. In testing, is there a way to automatically select all unprotected cells or to highlight them. I know that I can tab through the page and excel will only stop on unprotected cells, but the sheets are too big for this approach.

    As always, your ideas are appreciated.

  2. #2
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting/highlighting only unprotected cells?

    Something like this??

    Sub test()

    Dim NewSelection As String
    Dim Cell As Object

    For Each Cell In Selection
    If Range(Cell.Address).Locked = true Then
    Else
    NewSelection = NewSelection & "," & Cell.Address
    End If
    Next

    NewSelection = Mid(NewSelection, 3, Len(NewSelection))

    Range(NewSelection).Select

    End Sub

    Hope this helps

    Jacob

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Denver, Colorado
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting/highlighting only unprotected cells?

    That sir, is wonderful.

    (now what do I do with it?)

  4. #4
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting/highlighting only unprotected cells?

    Hi serickson,

    Here is another slightly different approach. This macro selects all the unlocked cells on the active worksheet WITHIN THE USED RANGE. The used range is the smallest rectangular range of cells that contain data. This definition is important because it is possible if the worksheet doesn't have any filled cells around the unlocked cells that the cells will not be within the used range and therefore not be highlighted.

    To use this macro, first insert it into a macro module by going to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM), and paste the code into the code pane. To run the macro use Alt-TMM or assign it to a button or keyboard shortcut.


    Sub SelectUnlocked()
    'Selects all unlocked cells within used range on the active worksheet
    Dim Cell As Range
    Dim UnlockedCells As Range
    Dim CellCount As Long
    CellCount = 0
    For Each Cell In ActiveSheet.UsedRange
    If Not Cell.Locked Then
    If CellCount = 0 Then
    Set UnlockedCells = Cell
    Else
    Set UnlockedCells = Union(UnlockedCells, Cell)
    End If
    CellCount = CellCount + 1
    End If
    Next Cell
    UnlockedCells.Select
    'Comment this out if you don't want to see the count:
    MsgBox CellCount & " unlocked cells", vbInformation, "Select Unlocked Cells"
    End Sub
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  5. #5
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,853
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Selecting/highlighting only unprotected cells?

      
    When do you want it to activate

    You can put it in the sheet_activate module in vba

    Just add range(MyRange).select to the top of the code. Replace MyRange with your range that you use. The code will then run whenever the sheet is activated.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com