0

I Should be able to edit any text updated except , "Posted", "Closed" in J Column. Every time I edit the text the date should be auto updated in next column cell. For example, I need to change text "Obsolete" in J3 Column tomorrow and tomorrow date should be updated in L3 cell as before cell K3 is occupied with today's update (Below is the code )

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim x As Integer
    
    x = 2
    With Sheets("sheet2")
        .Unprotect "test"
        .Cells.Locked = False
    End With
    
    For x = 2 To 32
        If WorksheetFunction.CountA(Range(Cells(x, 1), Cells(x, 10))) <> 0 And Cells(x, 11).Value = "" Then
            Cells(x, 11).Value = Date
        Else: GoTo ende
        End If
        ende:
    Next x
    Call nottoday
End Sub

Sub nottoday()
    For x = 2 To 32
        If IsDate(Cells(x, 11).Value) And Cells(x, 11).Value < Date Then
            ActiveSheet.Unprotect Password:="test"
            Range(Cells(x, 1), Cells(x, 11)).Locked = True
        End If
    Next x
    Sheets("Sheet2").Protect "test"
End Sub

enter image description here

2
  • Can anyone please help Commented Jan 6 at 14:28
  • I don't see anywhere in the code a test to check if the cell is (not) "Posted" or "Closed". Also, not sure why there is a need to loop through 31(?) cells to check/update date. I think it would be enough to update the date if the cell changed. Other than that you need to be more specific as to where your code is not working to your expectation.
    – gns100
    Commented Jan 8 at 16:08

0

You must log in to answer this question.