Macro to display locked cells?

[9tanstaafl9]

Does anyone have a macro already written (or know where I could find one) that would somehow indicate which cells on a spreadsheet are locked? Or can that be done with conditional formatting? I would think there is probably an easy way to do this, but I can't find it.

It's okay if it overwrites any background coloring already there, I could check on a copy.

Thanks!
Jennifer

 

[jproffer]

Sub SelectUnlockedCells()
    On Error Resume Next
       ActiveWorkbook.Activate
          If Err <> 0 Then
            MsgBox "There are no open workbooks"
          Else
          End If
    Dim WorkRange As Range
    Dim FoundCells As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        If Cell.Locked = False Then
            If FoundCells Is Nothing Then
                Set FoundCells = Cell
            Else
                Set FoundCells = Union(FoundCells, Cell)
            End If
        End If
    Next Cell
    If FoundCells Is Nothing Then
        MsgBox "All cells are locked."

    Else
        FoundCells.Select
    End If
End Sub

To select unlocked cells...or:

Sub SelectLockedCells()
    On Error Resume Next
       ActiveWorkbook.Activate
          If Err <> 0 Then
            MsgBox "There are no open workbooks"
          Else
          End If
    Dim WorkRange As Range
    Dim FoundCells As Range
    Dim Cell As Range
    Set WorkRange = ActiveSheet.UsedRange
    For Each Cell In WorkRange
        If Cell.Locked = True Then
            If FoundCells Is Nothing Then
                Set FoundCells = Cell
            Else
                Set FoundCells = Union(FoundCells, Cell)
            End If
        End If
    Next Cell
    If FoundCells Is Nothing Then
        MsgBox "All cells are unlocked."

    Else
        FoundCells.Select
    End If
End Sub

to select all locked cells.

This is only selecting, not filling the interior of the cell. I'm sure with a little change it can though .

 

[Colin Legg]

Just use conditional formatting. You can tell if a cell is locked using the CELL function.

Conditional Formatting Formula:

=CELL("Protect",A1)=1

HTH,
Colin

 

[MickG]

bump

 

[texasalynn]

Excel defaults that all cells are locked it just has no effect until the sheet is protected, so I think you may be asking about a protected sheet? So if the cell is locked no conditional formatting can be done.

 

[9tanstaafl9]

Thank you everyone! I appreciate your help. You've just saved me a lot of work, I only feel foolish that I wasted so much time before I asked for advice, or even considered that there was a better way than checking manually.

I wonder if there's a post anywhere of the top things people waste time doing in Excel manually that they would be better off doing with code or shortcut keys...

Thanks again,
Jennifer