If Not Intersect (Target.Range) Logic

[lnagel]

I have 30 Rows of Data which the user manipulates by selecting a value from a drop down list in Column E
One of the Drop Down Values is "need". If the user selects "need" or types "need" then I clear all data in that row for columns A, C, D, G, & H
This works fine EXCEPT if the user COPIES "need" and pastes that value to multiple rows in which case only the 1st row copied to functions as expected. Subsequent rows do not owrk.

This is the code that I am currently using :

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   Application.EnableEvents = False
  '_________________________________________________________________________________________________________
  If Not Intersect(Target, Range("E:E")) Is Nothing Then
      If LCase(Target.Value) = "need" Then Intersect(Target.EntireRow, Range("A:A, C:D, G:H")).ClearContents
      If LCase(Target.Value) = "need" Then
      With Range("A" & Target.Row)
      .Value = Range(.Validation.Formula1)(1).Value
      End With
      End If
   ElseIf Not Intersect(Target, Range("C:D, G:H")) Is Nothing Then
      If LCase(Range("E" & Target.Row)) = "need" Then Target.Value = ""
   End If

Thanks in advance for your assitance

 

[lnagel]

Re: If Not Intersect (Target.Range) Logic - Need Help

One other thing - I initially thought that all I needed to do was adjust Target.countlarge value - but that really doesnt have any effect

Thanks

 

[Peter_SSs]

Re: If Not Intersect (Target.Range) Logic - Need Help

If the user selects "need" or types "need" then I clear all data in that row for columns A, C, D, G, & H

Seems like there is actually a bit more than that going on with the code, but try this (untested) in a copy of your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range

  Set Changed = Intersect(Target, Columns("E"))
  If Not Changed Is Nothing Then
      Application.EnableEvents = False
      For Each c In Changed
        If LCase(c.Value) = "need" Then
          Intersect(c.EntireRow, Range("A:A, C:D, G:H")).ClearContents
          With Range("A" & c.Row)
            .Value = Range(.Validation.Formula1)(1).Value
          End With
        End If
      Next c
  End If
  
  Set Changed = Intersect(Target, Range("C:D, G:H"))
   If Not Changed Is Nothing Then
      Application.EnableEvents = False
      For Each c In Changed
        If LCase(Range("E" & c.Row)) = "need" Then c.ClearContents
      Next c
      Application.EnableEvents = True
   End If

End Sub

 

[lnagel]

Re: If Not Intersect (Target.Range) Logic - Need Help

Peter

Copy & Paste (or even single entry) of "need" never gets to :

Set Changed = Intersect(Target, Columns("E"))

Code never gets to
MsgBox "Changed = " & Changed
placed immediately after "Set Changed = " code

Yes you are correct on the " a bit additional" - I am doing with Column A (which also has a drop down list associated with it)
IF "need" in Column E (clear contents of C:D & G:H)
then also Column A is set to the 1st Value of the Drop Down List associated with A

 

[Peter_SSs]

Re: If Not Intersect (Target.Range) Logic - Need Help

Copy & Paste (or even single entry) of "need" never gets to :

Set Changed = Intersect(Target, Columns("E"))

If the code never gets to that first line, then it sounds like your 'events' are disabled. Close completely out of Excel and try again, or run this code

Sub EE()
  Application.EnableEvents = True
End Sub

What exactly is the Data validation in column E?

 

[lnagel]

Re: If Not Intersect (Target.Range) Logic - Need Help

Yep - you were correct on the events being disabled

Fixed and the code does process - however functions pretty much the same as original code where individual entry of "need" into column E works fine. Copy & Paste of "need" into multiple rows of Column E still seems to simply kick out of sub.

I'm still thinking the "If target.countlarge > 1 then Exit Sub" has something to do with that?

Doesnt seem to make a difference if I adjust the value > 1 though

Maybe I'm not understanding the functionality of the target.countlarge code?

Anyway - Data Validation for E consists of either "need" or "acquired"

thanks

 

[Peter_SSs]

Re: If Not Intersect (Target.Range) Logic - Need Help

Yep - you were correct on the events being disabled

Fixed and the code does process - however functions pretty much the same as original code where individual entry of "need" into column E works fine. Copy & Paste of "need" into multiple rows of Column E still seems to simply kick out of sub.

I'm still thinking the "If target.countlarge > 1 then Exit Sub" has something to do with that?

My code does not have that line in it. Try using my code, not yours.

 

[lnagel]

Re: If Not Intersect (Target.Range) Logic - Need Help

Peter - this is the thread - Thanks

 

[Peter_SSs]

Re: If Not Intersect (Target.Range) Logic - Need Help

Peter - this is the thread - Thanks

You are welcome. Sounds like all is sorted now.