Intersect command fails with different sheet ranges

[darthslayer]

I'm trying to create an self updating drop down list. To achive this I found a code and adapted it to my workbook. Code works if it's in the sheet named "default" but when I try to redirect it to another sheet, it stucks in the code below, with this error (method 'intersect' object' _global failed"

If Intersect(Target, Worksheets("default").Range("c" & count_cells + 1)) Is Nothing Then

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Dim count_cells As Integer
    Dim new_value As String
    Dim old_value As String
    For count_cells = 1 To Worksheets("default").Range("c2:c400").CurrentRegion.Rows.Count - 1
        If Intersect(Target, Worksheets("default").Range("c" & count_cells + 1)) Is Nothing Then
        Else
            Application.EnableEvents = False
            new_value = Target.Value
            Application.Undo
            old_value = Target.Value
            Target.Value = new_value
            Range("AS:AS").Select
            Selection.Replace What:=old_value, Replacement:=new_value, LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
            Target.Select
        End If
    Next count_cells
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[RoryA]

It's not possible for a range on one sheet to intersect (be within) a range on another sheet. What exactly are you trying to do?

BTW, if you're going to cross post on other forums, which is OK, please provide links to those posts so that we don't duplicate effort that may already have been put in elsewhere.