Locked Cells

[tintin1012000]

Hi all,

I want to limit what cells can have data enterend by an operator,
What I have done is locked all cells in the sheet and unlocked the cells that I want data entered,The I protect the sheet,Is there code so that the unlocked cells have a diffrennt background colour to that of the the locked cells ?

 

[Sektor]

Sub G()
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange
        cell.Interior.Color = IIf(cell.Locked, vbYellow, vbRed)
    Next
End Sub

 

[brettdj]

You could use this code to quickly format the unlocked cells on the activesheet as red

This line formats the unlocked cell as red
rng3.Interior.Color = vbRed

Originally posted at http://www.experts-exchange.com/A_2769.html

hth

Dave

Sub QuickUnlocked()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim lCalc As Long
    Dim bWorkbookProtected

    On Error Resume Next
    'test to see if WorkBook structure is protected
    'if so try to unlock it
    If ActiveWorkbook.ProtectStructure Then
        ActiveWorkbook.Unprotect
        If ActiveWorkbook.ProtectStructure Then
            MsgBox "Sorry, I could not remove the passsword protection from the workbook" _
                 & vbNewLine & "Please remove it before running the code again", vbCritical
            Exit Sub
        Else
            bWorkbookProtected = True
        End If
    End If

    Set ws1 = ActiveSheet
    'test to see if current sheet is protected
    'if so try to unlock it
    If ws1.ProtectContents Then
        ws1.Unprotect
        If ws1.ProtectContents Then
            MsgBox "Sorry, I could not remove the passsword protection from sheet" & vbNewLine & ws1.Name _
                 & vbNewLine & "Please remove it before running the code again", vbCritical
            Exit Sub
        End If
    End If
    On Error GoTo 0

    'disable screenupdating, event code and warning messages.
    'set calculation to manual
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = False
        lCalc = .Calculation
        .Calculation = xlCalculationManual
    End With

    On Error Resume Next
    'check for existing error cells
    Set rng1 = ws1.Cells.SpecialCells(xlCellTypeFormulas, 16)
    On Error GoTo 0

    'copy the activesheet to a new working sheet
    ws1.Copy After:=Sheets(Sheets.Count)
    Set ws2 = ActiveSheet
    'delete any cells that already contain errors
    If Not rng1 Is Nothing Then ws2.Range(rng1.Address).ClearContents

    'protect the new sheet
    ws2.Protect
    'add an error formula to all unlocked cells in the used range
    'then use SpecialCells to read the unlocked range address
    On Error Resume Next
    ws2.UsedRange.Formula = "=NA()"
    ws2.Unprotect
    Set rng2 = ws2.Cells.SpecialCells(xlCellTypeFormulas, 16)
    Set rng3 = ws1.Range(rng2.Address)
    ws2.Delete
    On Error GoTo 0

    'if WorkBook level protection was removed then reinstall it
    If bWorkbookProtected Then ActiveWorkbook.Protect

    'cleanup user interface and settings
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        lCalc = .Calculation
    End With

    'inform the user of the unlocked cell range
    If Not rng3 Is Nothing Then
        MsgBox "The unlocked cell range in Sheet " & vbNewLine & ws1.Name & " is " & vbNewLine & rng3.Address(0, 0)
        rng3.Interior.Color = vbRed
    Else
        MsgBox "No unlocked cells exist in " & ws1.Name
    End If

End Sub

 

[tintin1012000]

Thanks for qucik reply,
Sektor I went with your option,

I want to leave the locked cells alone and I would like to have the unlocked cells in a lighter colour, Can you tell me what colours i can use instead of vbRed

Sub G()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
cell.Interior.Color = IIf(cell.Locked, vbYellow, vbRed)
Next
End Sub

 

[tintin1012000]

anyone help on this