Worksheet Change Event Firing Outside of Range

[Nadine]

Hello and thank you for any attention my post may receive

I have modified the code below (which was given to me through a previous post) to suit my current project.....without success.

The code is doing/not doing a couple of things.
1. returning 'Run-time error 424 - Object required' whenever I update a cell outside of the target range.
2. not firing when I update a cell within the target range.

Debug is pointing to the 'red' text line of code below.
The value in '.Offset(0, -6)' is a formula calculated value.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim Cell As Range
    Set rng = Intersect(Target, Range("H:H"))
    [COLOR=rgb(184, 49, 47)][B]For Each Cell In rng[/B][/COLOR]
        If Not IsEmpty(Cell) And Not IsEmpty(Cell.Offset(1, 0)) Then
            With Cell
                If .Offset(0, -6).Value = 1 Then
                    With .Resize(1, 18).Offset(0, -6).Borders(xlEdgeTop)
                        .linestyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                End If
            End With
        End If
    Next Cell
End Sub

Thank you!

 

[Nadine]

The first response will only work when one cell only is changed (in column H).
The code I posted allows for more than one cell in column H to be changed (as in copy/paste, dragging, Ctrl+Enter, etc.), which in line with your original code.

Oh I see. I shall revisit my workbook and change it.

Thank you very much for pointing that out to me.

 

[Peter_SSs]

Oh I see. Clearly a rookie to this forum .

That is no problem, but the information is contained in #15 of the Forum Rules