How do I set a 'Worksheet_Change' event for part of a worksheet?

[Phil Payne]

Hello all,

I am very much a VBA beginner and I cannot see a way to modify my code. I hope someone can help.

The first part of the code below checks for a change of Status in the Status column and on change fills the adjacent Cost cell with appropriate colour then enters current date into the adjacent Date cell. The range covered extends from column ‘R’ (first Status column), to column ‘EZ’ (last Status column). The second part records the date any part of a row was changed and who changed it.

This code operates well but it works from top to bottom of each column.

I want to use the top 14 rows of the worksheet for other calculations so I need to have this 'change event' restricted to Rows 15 down to last row only.

Can anyone advise how I adjust my code to achieve this?

Thanks.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Dim rCell As Range
    Dim rCodes As Range
    Dim rRow As Range
    Dim vMatch

    Set rCodes = Range("E2:E12")

    If (Target.Column >= 18) And (Target.Column <= Range("EZ1").Column) And (Target.Column Mod 3 = 0) Then
        If Len(Target.Value) > 0 Then
            On Error Resume Next
            vMatch = Application.Match(Target.Value, rCodes, 0)
            If IsError(vMatch) Then
                MsgBox "Invalid code selected"
            Else
                With Target
                    .Offset(, 1).Interior.Color = rCodes.Cells(vMatch).Interior.Color
                    .Offset(0, 2).Value = Date
                End With
            End If
        End If
    End If
    
'CODE TO ENTER DATE ROW CHANGED IN CELL IN SAME ROW AT COLUMN [FG]
' The range covered extends from column ‘R’ (first Status column), to column ‘FA’ (last COST column).
' If adding columns adjust ranges accordingly!


   If Not Intersect(Target, Range("R:fb")) Is Nothing Then
      For Each rCell In Intersect(Target, Range("R:fb")).Cells
        If Target.Row > 1 Then Cells(Target.Row, "FG") = Now()
        If Target.Row > 1 Then Cells(Target.Row, "FH") = Environ("Username")
        ' THESE DESTINATION CELLS IN WORKSHEET MUST HAVE SECURITY PROTECTION OFF"
      Next rCell
   End If

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

 

[VoG]

Maybe like this

        Private Sub Worksheet_Change(ByVal Target As Range)

            If Target.Count > 1 Then Exit Sub
            If Target.Row < 15 Then Exit Sub
            With Application
                .ScreenUpdating = False
                .EnableEvents = False
            End With

 

[Phil Payne]

HelloVoG

Thanks very much - that works to allow me to use the top rows and is much simpler than the solution I just worked out, i.e. (.... If (Target.Column >= 18) And (Target.Column <= Range("EZ1").Column) And (Target.Column Mod 3 = 0) And (Target.Row >= 15) And (Target.Row <= 139) Then ......)

Unfortunately it does not stop the event from happening below the last row.
In my amended code I have had to specifically state the last row number of the range to ensure this happens however, as the table may 'grow' over time, I need to use something like xlDown but can't work out how!

Thanks very much for taking the time to assist.

Regards,