Intersect error

[Patriot2879]

Hi all, good morning, I have the code below but the workrng intersect doesn't work it comes up with a yellow background can you help please.

this is the line with the error

Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)

Below is the full code

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)
xOffsetColumn = -7
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd/mm/yyyy hh:mm"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

 

[Patriot2879]

Re: Intersect error please help

sorry forgot to mention this code is located in Sheet8 (SMART locate)

 

[RoryA]

Re: Intersect error please help

That would suggest that a different worksheet is active. You should really use:

Set WorkRng = Intersect(Range("I:I"), Target)

so that the Range always refers to the sheet containing the code.

 

[Patriot2879]

Re: Intersect error please help

that's great thank you for your help