(Solved) – Why are my attempts at creating a dynamic vba range for only the first section of data failing?

(solved)-–-why-are-my-attempts-at-creating-a-dynamic-vba-range-for-only-the-first-section-of-data-failing?

To protect a second section of dynamic data when rows are removed in the first section, I need to change the last section of existing code below to a dynamic range that begins at E3 and ends either at the first row where column E is blank, last row where it is => zero or use a dynamic cell reference (N2) that shows # of last row (or anything that will work). At present, I handle this new need by manually changing E10001 to the new end of the first section of data (i.e, E5006). All of my attempts (used every option I could find) at this dynamic code resulted in the date being inserted 3 columns to right of any entry I make in my test spreadsheet. Thanks in advance for any help.

Private Sub Worksheet_Change(ByVal Target As Range)

    Target.Worksheet.Unprotect Password:="Midnight"

    On Error Resume Next

    Application.EnableEvents = True

    Dim hng As Range
    Set hng = Range("F3:F10001")

    If Intersect(Target, hng) Is Nothing Then
        Target.Offset(-1, -4).Locked = True
    End If

    Application.EnableEvents = True

    Dim xng As Range
    Set xng = Range("F3:F10001")

    If Intersect(Target, xng) Is Nothing Then
        Target.Offset(-1, -3).Locked = True
    End If

    Application.EnableEvents = True

    Dim wng As Range
    Set wng = Range("F3:F10001")

    If Intersect(Target, wng) Is Nothing Then
        Target.Offset(-1, -2).Locked = True
    End If

    Application.EnableEvents = True

    Dim qng As Range
    Set qng = Range("F3:F10001")

    If Intersect(Target, qng) Is Nothing Then
        Target.Offset(-1, -1).Locked = True
    End If

    Application.EnableEvents = True

    Dim sng As Range
    Set sng = Range("F3:F10001")

    If Intersect(Target, sng) Is Nothing Then
        Target.Offset(-1, 0).Locked = True
    End If

    Dim cng As Range
    Set cng = Range("B3:C10001")
    If Not Intersect(Target, cng) Is Nothing Then
        Application.EnableEvents = False
            Target.Value = UCase(Target.Value)
        Application.EnableEvents = True
    End If

    Dim rng As Range
    Set rng = Range("E3:E10001")

    If Not Intersect(Target, rng) Is Nothing Then
        Target.Offset(0, 3) = Now

    End If
    Target.Worksheet.Protect Password:="Midnight"
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *