Private Sub Target Question

[TGwilding]

I am working with the following code:
Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub


If Intersect(Target, Range("u11:u32")) Is Nothing Then Exit Sub

Application.EnableEvents = False


If Target <> "" Then Range("t69").Select
Application.CutCopyMode = False
Selection.Copy
Range("s69").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("u9").Select
Application.EnableEvents = True


If Target.Cells.Count > 1 Then Exit Sub


If Intersect(Target, Range("y11:y32")) Is Nothing Then Exit Sub

Application.EnableEvents = False


If Target <> "" Then Range("x69").Select
Application.CutCopyMode = False
Selection.Copy
Range("w69").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("y9").Select
Application.EnableEvents = True

End Sub

The first part is working perfectly, but when I change values in range y11:y32, nothing happens.

Can anyone help on this?
thanks

 

[just_jon]

This line

If Intersect(Target, Range("u11:u32")) Is Nothing Then Exit Sub

ells the code to ignore any change not in U11:U32

If you want to monitor a different range, change the reference to that range reference.

EDIT - AActually, try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    If Not Intersect(Target, Range("u11:u32")) Is Nothing Then
        If Target <> "" Then Range("s69").Value = Range("t69").Value
        Range("u9").Select
    ElseIf Not Intersect(Target, Range("y11:y32")) Is Nothing Then
        If Target <> "" Then Range("s69").Value = Range("x69").Value
        Range("y9").Select
    End If
    Application.EnableEvents = True
End Sub

 

[maytas]

Hi.

Replace

If Intersect(Target, Range("u11:u32")) Is Nothing Then Exit Sub

with

If Not Intersect(Target, Range("u11:u32")) Is Nothing Then
 '*****************************************************

So Replace

 If Intersect(Target, Range("y11:y32")) Is Nothing Then Exit Sub

with

 If Not Intersect(Target, Range("y11:y32")) Is Nothing Then 
'**********************************************

 

[Smitty]

How's this:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
         <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#007F00">'   Set Target Range</SPAN>
        Set rng = Range("U11:U32, Y11:Y32")
        <SPAN style="color:#007F00">'   Only look at that range</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN>
            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Column
                <SPAN style="color:#00007F">Case</SPAN> 21
                    <SPAN style="color:#00007F">If</SPAN> Target <> "" <SPAN style="color:#00007F">Then</SPAN> Range("S69").Value = Range("T69").Value
                <SPAN style="color:#00007F">Case</SPAN> 25
                    <SPAN style="color:#00007F">If</SPAN> Target <> "" <SPAN style="color:#00007F">Then</SPAN> Range("W69").Value = Range("X69").Value
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[maytas]

Hi,
Full Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Stop
    If Not Intersect(Target, Range("u11:u32")) Is Nothing Then
        If Target <> "" Then Range("t69").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("s69").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("u9").Select
        Application.EnableEvents = True
    ElseIf Intersect(Target, Range("y11:y32")) Is Nothing Then Exit Sub
        If Target <> "" Then Range("x69").Select
        Application.CutCopyMode = False
        Selection.Copy
        Range("w69").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("y9").Select
    End If
    Application.EnableEvents = True
End Sub

 

[TGwilding]

Thanks Everyone - just_jon's code is exactly what I needed.
thanks