VBA to create a list of all unlocked cells

[Arie Bos]

Hello Excelleers,

I would like to create a list of all unlocked cells to easier keep track of them. Or, alternatively, a code that lightens up every unlocked cell, until next keypress.
I like to avoid conditional formatting, as it slows down the sheet and I only want it during building the workbook.
I thought his topic would already exist, as it seems handy if one could put such a code in any workbook, but I could not find it in the forum.

Many thanks,

 

[dmt32]

Hi,
one idea maybe?

Sub UnLockedCells()
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange.Cells
        cell.Interior.Color = IIf(Not cell.Locked, vbYellow, xlNone)
    Next cell
End Sub

Dave

 

[Arie Bos]

Thanks Dave, that was quick!. it works nice. I have made two cons in the quick access toolbar with one for this code, and one to bring it all back to no color.
Works perfect, thanks again.
Arie

 

[dmt32]

Thanks Dave, that was quick!. it works nice. I have made two cons in the quick access toolbar with one for this code, and one to bring it all back to no color.
Works perfect, thanks again.
Arie

code should return to no colour if cell state = locked

Glad solution helps

Dave