How to make cell as read only in Excel?

In many cases, you may need to prevent others from editing some specified cells in your worksheet. This article provides two methods to help you make specified cells as read only, so that others cannot edit these cells except for reading.

Make cell as read only by protecting worksheet
Make cell as read only by VBA code without protecting worksheet

Make cell as read only by protecting worksheet

You can only lock the cells which you want to make them read-only, and then protect this worksheet to achieve it. Please do as follows.

1. Click the button on the top left corner of current worksheet to select the whole cells.

2. Press the Ctrl + 1 keys simultaneously to open the Format Cells dialog box. In the dialog box, uncheck the Locked box under the Protection tab, and then click the OK button. See screenshot:

3. Now select the cells you want to make them as read only (here I select cells of range A1:C18). Press the Ctrl + 1 keys simultaneously to open the Format Cells dialog box, check the Locked box under the Protection tab and click the OK button.

4. Go ahead to click Review > Protect Sheet. See screenshot:

5. Specify and confirm your password to protect the worksheet as below screenshot shown.

Now cells in the locked range are read-only now. When you try to edit these specific cells inside the range, you will get a prompt box as below screenshot shown.

Make cell as read only by VBA code without protecting worksheet

If you don’t want to protect the worksheet to make cell as read only. The VBA script in this section will help you to solve this problem easily.

1. Right click the sheet tab you need to make cell as read only, and then click View Code from the right-clicking menu.

2. In the opening Microsoft Visual Basic for Applications window, copy and paste the below VBA code into the Code window.

VBA code: Make cell as read only in Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 Then
        If Target.Row = 3 Or Target.Row = 4 Or Target.Row = 5 Then
            Beep
            Cells(Target.Row, Target.Column).Offset(0, 1).Select
            MsgBox Cells(Target.Row, Target.Column).Address & " cannot be selected and edited as it is a read-only cell", _
            vbInformation, "Kutools for Excel"
        End If
    End If
End Sub

Note: This code can just make specified cells in one column as read only. In this code, it will make cell A3, A4 and A5 as read only in current worksheet.

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

When clicking on cell A3, A4 or A5 in current worksheet, you will get a Kutools for Excel dialog box as below screenshot shown, and the cursor will move to the right adjacent cell automatically.