How to protect cells based on date in Excel?

Normally, we can protect the worksheet to prevent others editing or modifying the cell values, but, sometimes, you need to protect the cells based on date. For example, I want to protect other cells but only allow the cell values of today’s date to be modified as following screenshot shown, this article will talk about how to protect cells based on date.

doc protect by date 1

Protect all rows except today’s date row with VBA code

Protect all rows the date has passed with VBA code


arrow blue right bubble Protect all rows except today’s date row with VBA code

Only allow the row which equal today’s date to be modified, the following code can help you, please do as this:

1. Right click the sheet tab that you want to protect the cells based on date, and then choose View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module:

VBA code: Protect all rows except today’s date row:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20161025
    If Range("E" & Selection.Row).Value <> Date Then
        ActiveSheet.Protect Password:="111111"
        MsgBox "Only today's date row can be edited!", vbInformation, "Kutools for Excel"
    ElseIf Range("E" & Selection.Row).Value = Date Then
        ActiveSheet.Unprotect Password:="111111"
        ActiveSheet.EnableSelection = xlNoRestrictions
    End If
End Sub

doc protect by date 2

Note: In the above code, the letter E is the column header where the date located, “111111” is the password to protect this sheet. You can change them to your need.

2. Then save and close this code window.

(1.) If you click other cells beyond today’s date row, an prompt box will pop out to remind you the cell can’t be edited, see screenshot:

doc protect by date 3

(2.) If you click and edit the row equals today’s date, it will be modified successfully, see screenshot:

doc protect by date 4


arrow blue right bubble Protect all rows the date has passed with VBA code

If you need to protect all rows the date has passed, only allow today and future date rows to be modified, please apply the following VBA code:

1. Right click the sheet tab that you want to protect the cells based on date, and then choose View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module:

VBA code: Protect all rows date has passed:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updateby Extendoffice 20161025
  Dim xRow As Long
  xRow = 2
  ThisWorkbook.ActiveSheet.Unprotect Password:="111111"
  ThisWorkbook.ActiveSheet.Cells.Locked = False
  Do Until IsEmpty(Cells(xRow, 5))
    If Cells(xRow, 5) < Date Then
      Rows(xRow).Locked = True
    End If
    xRow = xRow + 1
  Loop
  ThisWorkbook.ActiveSheet.Protect Password:="111111"
End Sub

doc protect by date 5

Note: In the above code, the number 5 is the column number where the date located, “111111” is the password to protect this sheet. You can change them to your need.

3. Then save and close this code window.

(1.) If you click the cells date has passed, an prompt box will pop out to remind you the cell can’t be edited, see screenshot:

doc protect by date 6

(2.) If you click the cell of rows to try to change the values in today’s date or future date, it will be modified successfully, see screenshot:

doc protect by date 7


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    avtandil · 5 months ago
    hello i did it but when i press cell message shows that it can not be edited but when i press keyborad i still can write in the cell

  • To post as a guest, your comment is unpublished.
    mouzahem · 11 months ago
    Hello, I tried to do the same but to Protect all columns except today’s date column with VBA code by replacing "Selection.Row" with "Selection.Columns" and "E" with "5" (row number where dates are), but I keep receiving error 1004!

    Any help please?

    Thank you
    • To post as a guest, your comment is unpublished.
      skyyang · 11 months ago
      Hello,
      To apply this code to columns that you need, please use the following VBA code:(Note: In the code, 5 is the row number contains the date, please change it to your need.)

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Dim xRg As Range
      Set xRg = Target.Worksheet.Cells(5, Target.Column)
      If xRg <> Date Then
      ActiveSheet.Protect Password:="111111"
      MsgBox "Only today's date row can be edited!", vbInformation, "Kutools for Excel"
      ElseIf xRg.Value = Date Then
      ActiveSheet.Unprotect Password:="111111"
      ActiveSheet.EnableSelection = xlNoRestrictions
      End If
      End Sub

      Please try, hope it can help you!
      • To post as a guest, your comment is unpublished.
        rohit Khanna · 6 months ago
        Hi Im facing one problem, once i close the file and reopens it again on the same date, code doesnot work. pls help im stuck

        • To post as a guest, your comment is unpublished.
          skyyang · 5 months ago
          Hi, rohit,
          To keep the code, you should save your workbook as Excel Macro-Enabled Workbook format, please try, thank you!
      • To post as a guest, your comment is unpublished.
        rohit Khanna · 6 months ago
        Thanks for your help, Thanku so much buddy, It is a great help. and solved my tension.

        Be safe

        Regards
        Rohit Khanna
      • To post as a guest, your comment is unpublished.
        youssef · 11 months ago
        Thank you! Sorry, I did not get your reply until now, it might be a problem in my email.

        It worked! Can't say thank you enough! I really appreciate that!
        • To post as a guest, your comment is unpublished.
          skyyang · 11 months ago
          Hi,
          Glad it can help you!
          • To post as a guest, your comment is unpublished.
            mouzahem · 11 months ago
            Hi,

            I am trying to expand this to protect columns that do not include this current month or/and this current week. I tried to replace "Date" with "month" or "week", but did not work.

            I have attached a sample for weeks sheet where user cannot edit or type in columns other than this week.

            Any advice on that? or shall I use actual dates to reflect a day in the month?
  • To post as a guest, your comment is unpublished.
    Youssef · 11 months ago
    Hello, I tried to do the same but to Protect all columns except today’s date column with VBA code by replacing "Selection.Row" with "Selection.Columns" and "E" with "5" (row number where dates are), but I keep receiving error 1004!

    Any help please?

    Thank you
  • To post as a guest, your comment is unpublished.
    deokarkaran · 1 years ago
    Hi,

    while running this code I am getting an error as shown in the figure
    Also i am attaching a snapshot my excel where i want to make changes..

    Can u guide in my code where should i make changes according to my excel file so that cells that contain only todays & future date can be editied ???
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Karan,
      Because there are merged cells in your table, the above code can not be applied correctly in merged cells table.
      If you have any other good ideas, please comment here. Thank you!
  • To post as a guest, your comment is unpublished.
    Niall · 1 years ago
    As someone who is relatively new to VBA, would you mind explaining why xRow = 2 in Line 4?



    Thanks
    • To post as a guest, your comment is unpublished.
      Piotr · 1 years ago
      because in row no. 1 you have header
  • To post as a guest, your comment is unpublished.
    Bobby · 2 years ago
    This works great. but how can I switch it to unlock a column based on dates in row 3?
  • To post as a guest, your comment is unpublished.
    tajindia52@gmail.com · 2 years ago
    Hello.....


    I am using the code for Protect all rows except today’s date row........



    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Updateby Extendoffice 20161025
    If Range("A" & Selection.Row).Value <> Date Then
    ActiveSheet.Protect Password:="111111"
    MsgBox "Only today's date row can be edited!", vbInformation, "Kutools for Excel"
    ElseIf Range("E" & Selection.Row).Value = Date Then
    ActiveSheet.Unprotect Password:="111111"
    ActiveSheet.EnableSelection = xlNoRestrictions
    End If
    End Sub


    After i save i and click on cell i get message only today's date row can be edited....but if i double click the cell the i can edit...pls help
  • To post as a guest, your comment is unpublished.
    lmg777 · 3 years ago
    hi is it possible to lock certain cells depending on the date that is directly above them in a column?
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hi, Lusis,
      Can you give your problem more specifically? Or you can attach a screenshot as your problem.
      Thank you!
      • To post as a guest, your comment is unpublished.
        lmg777 · 3 years ago
        Hi skyyang

        Yes here is a screenshot. Hope this helps.
        • To post as a guest, your comment is unpublished.
          lmg777 · 3 years ago
          Here it is.
          • To post as a guest, your comment is unpublished.
            skyyang · 3 years ago
            Hello, Lucis,
            Do you mean to lock the columns which date is past, and only the columns of today and future days can be modified?
            • To post as a guest, your comment is unpublished.
              lmg777 · 3 years ago
              Correct! How would I go about that?
              • To post as a guest, your comment is unpublished.
                skyyang · 3 years ago
                Hi, Luis,
                How about the date cell in A1? Is it entered manually or entered with a formula =now()? If it is a formula cell, the locked columns will be changed with the date changes.
                Looking forward to your reply!
                • To post as a guest, your comment is unpublished.
                  lmg777 · 3 years ago
                  Hi Skyyangg

                  yes the cell A1 has the =now() formula.

                  thanks!
  • To post as a guest, your comment is unpublished.
    Lmg · 3 years ago
    Hi is it possible to lock columns by the date directly above the columns?
  • To post as a guest, your comment is unpublished.
    Hitesh · 3 years ago
    Loophole: Password can be seen by other person by using "View Code" option.