VBA to Paste into a range with locked cells, but skip locked cells

Question

I have a VBA script that searches a range in "Sheet1" for cells with yellow colour (6) and locks those cells. These cells are intentionally protected so that they are not able to be changed. My script then copies a range in "Sheet2" and pastes it into "Sheet1" however I get the error message to say that the cells are protected. What I need is for the script to skip the cells that are locked in "Sheet1" but pastes to all other cells in that range that are unlocked. I want the integrity of the locked cells to remain the same. this is what I have so far:

Sub lockcellsbycolor()

    Dim colorIndex As Integer
    colorIndex = 6
    Dim xRg As Range
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect

    For Each xRg In ActiveSheet.Range("A1:D40").Cells
        Dim color As Long
        color = xRg.Interior.colorIndex
        If (color = colorIndex) Then
            xRg.Locked = True
        Else
            xRg.Locked = False
        End If
    Next xRg
    Application.ScreenUpdating = True
    ActiveSheet.Unprotect
    MsgBox "All specified colour cells have been locked!"
    ActiveSheet.Protect

'grab data from sheet 2 and paste into "Sheet1"
    Sheets("Sheet2").Select
    Range("A1:D40").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'I need this paste to ignore locked cells - meaning any cell that's locked is not pasted over the top of but rather skipped. (See picture for an example of the desired outcome)

End Sub

you will need to iterate over each cell in turn and figure out if it is locked, and if it isn't paste the corresponding cell — teylyn
I thought that may be the case. Any ideas how that might look in VBA language incorporating what I have so far? — shano
Try some of the similar questions in the side panel or from a search engine. — teylyn

DisplayName DisplayName · Accepted Answer · 2018-04-19T07:01:07

you're unnecessarily iterating twice: just copy values in not yellowed cells

Option Explicit

Sub lockcellsbycolor()
    Dim colorIndex As Integer
    colorIndex = 6
    Dim xRg As Range

    Application.ScreenUpdating = False
    ActiveSheet.Unprotect

    For Each xRg In Sheets("Sheet1").Range("A1:D40").Cells
        Dim color As Long
        color = xRg.Interior.colorIndex
        If color <> colorIndex Then xRg.Value = Sheets("Sheet2").Range(xRg.Address).Value
    Next
End Sub

VBA to Paste into a range with locked cells, but skip locked cells

1 Answers