Making Certain Cells Uneditable

Question

I have this code that greys out part of column D if column E is greyed out, and makes column E colored and vice versa.

Instead of making it grey, I would like to make the cells uneditable.

Is that possible?

Dim sourceCell1 As Range, targetRng1 As Range, sourceCell2 As Range, targetRng2 
 As Range
 With Worksheets("SHELLY")
 If .Range("D3:D19").Interior.ColorIndex = 15 Then
    Set sourceCell1 = .Range("C41")
    Set targetRng1 = .Range("F3:F19")
    Set sourceCell2 = .Range("C42")
    Set targetRng2 = .Range("D3:D19")
 Else
    Set sourceCell1 = .Range("C41")
    Set targetRng1 = .Range("D3:F19")
    Set sourceCell2 = .Range("C43")
    Set targetRng2 = .Range("F3:F19")
 End If
 End With

sourceCell1.Copy
targetRng1.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

sourceCell2.Copy
targetRng2.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

You need to protect the sheet and all cells will be uneditable unless you unlock them. — SJR, Feb 13 '18 at 13:57
See [this post](https://stackoverflow.com/questions/3037400/how-to-lock-the-data-in-a-cell-in-excel-using-vba) — DisplayName, Feb 13 '18 at 14:08
@DisplayName Thanks, but I have seen that post. I tried and I am still unable to do it in the IF ELSE function as seen above. — The Gootch, Feb 13 '18 at 14:27

score 0 · Accepted Answer · answered Feb 13 '18 at 15:04

Expanding on the comment by @DisplayName, below the application of How to Lock the data in a cell in excel using vba.

Sub lockRange()
    Dim sourceCell1 As Range, targetRng1 As Range, sourceCell2 As Range, targetRng2 As Range

    With Worksheets("SHELLY")

        'unprotect sheet to make alterations
        .Unprotect Password:="p"
        'making range editable before protecting sheet
        .Range("A1:AA100").Locked = False

        If .Range("D3:D19").Interior.ColorIndex = 15 Then
            Set sourceCell1 = .Range("C41")
            Set targetRng1 = .Range("F3:F19")
            Set sourceCell2 = .Range("C42")
            Set targetRng2 = .Range("D3:D19")

            'defining which range to lock
            Set lockRng = .Range("D3:D19")
        Else
            Set sourceCell1 = .Range("C41")
            Set targetRng1 = .Range("D3:F19")
            Set sourceCell2 = .Range("C43")
            Set targetRng2 = .Range("F3:F19")

            'defining which range to lock
            Set lockRng = .Range("F3:F19")
        End If
    End With

    sourceCell1.Copy
    targetRng1.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    sourceCell2.Copy
    targetRng2.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    With Worksheets("SHELLY")
        'locking range so it is un-editable when protected
        lockRng.Locked = True
        'range E always gray/locked?
        .Range("E3:E19").Locked = True
        'protecting sheet with password
        .Protect UserInterfaceOnly:=True, Password:="p"
    End With
End Sub

I would like to ranges to take turns being locked so that only one is locked at a time. This code wiped out all formatting in columns D, E, and F — The Gootch, Feb 13 '18 at 17:29
The `lockRng` controls the locked ranges, you can alter to fit your needs. What do you mean it wiped out formatting? The only thing in this code that alters any type of formatting is your `.PasteSpecial` actions that you had already. — Miguel_Ryu, Feb 13 '18 at 17:57
I'm sorry I'm new at this - It wiped out all formatting because I had moved the cell that it was supposed to copy the formatting from, so it copied from a blank cell :/ — The Gootch, Feb 15 '18 at 14:10

Making Certain Cells Uneditable

1 Answers1