Here’s a utility to format unlocked cells using conditional formatting so that I can easily see that I’ve set the Locked property appropriately.
Dim sh As Worksheet
Dim rng As Range
Dim fc As FormatCondition
Dim lFcIndex As Long
Set sh = ActiveSheet
Set rng = sh.UsedRange
msFormula = “=NOT(CELL(““protect”“,” & rng.Cells(1).Address(0, 0) & “))”
If sh.ProtectContents Then
MsgBox “You can’t use this on a protected sheet.”
If rng.FormatConditions.Count = -1 Then
‘-1 means that not all cells have the same conditional formatting
MsgBox “You can’t use this when conditional formatting is present.”
If LockedShown(rng, lFcIndex) Then
‘lFcIndex will hold the number of the format condition
‘that has the right formula – the one to delete.
Set fc = rng.FormatConditions.Add(xlExpression, , msFormula)
fc.Interior.Color = RGB(204, 153, 255)
‘ugly purple that would never be used in a real spreadsheet
Private Function LockedShown(rng As Range, ByRef lFcIndex As Long) As Boolean
Dim i As Long
Dim bReturn As Boolean
bReturn = False
For i = 1 To rng.FormatConditions.Count
If rng.FormatConditions(i).Formula1 = msFormula Then
lFcIndex = i
bReturn = True
LockedShown = bReturn
It won’t work on a protected sheet or if there is conditional formatting already on the sheet. Well, that’s not entirely true. If there is conditional formatting that covers the whole used range, it will work. It only doesn’t work if the conditional formatting that’s present isn’t consistent across all cells in the used range.
I haven’t tested it in 2007 or 2010, but if you’d like to, leave a comment.