Whenever I try to highlight over column D I receive "Run-time error '13'". When I click debug it highlights this piece from the code,
If Target.Value = "Closed" Or Target.Value = "Closed" Then. I would greatly appreciate any advice on how to fix this issue.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wsd As Worksheet
Dim wsc As Worksheet
Application.ScreenUpdating = False
Set wsd = Sheets("Pipeline")
Set wsc = Sheets("Closed")
If Not Intersect(Target, Range("D6:D65536")) Is Nothing Then
If Target.Value = "Closed" Or Target.Value = "Closed" Then
erow = Target.Row
MsgBox "Moved to Closed"
numberofrow = wsc.Range("A65536").End(xlUp).Row
For i = 1 To numberofrow
If wsd.Cells(erow, 1) = wsc.Cells(i, 1) Then
wsd.Range("A" & erow & ":A" & erow).EntireRow.Copy wsc.Range("A" & numberofrow + 1)
Union(.Range(.Cells(Target.Row, "A"), .Cells(Target.Row, "V")), _
.Range(.Cells(Target.Row, "Y"), .Cells(Target.Row, .Columns.Count))).ClearContents
`enter code here`End With
ActiveSheet.Range("A2:D" & numberofrow + 1).Select
ActiveWorkbook.Worksheets("Closed").Sort.SortFields.Add Key:=Range("C2:C" & numberofrow + 1), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A2:D" & numberofrow + 1)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
I had to piece a few things together in order to get this to work, which is why it's pretty ugly. I couldn't figure out where to put guard clause. This so far has worked for what I need it for (aside from the Run-time error), transferring accounts to a separate sheet based on the sales stage entered into column D. Again, I appreciate all the help.
Your handler assumes only 1 cell is selected. When you highlight an entire column,
Target represents all selected cells, and then
Target.Value can't be meaningfully evaluated, so it raises a run-time error.
You need to modify your handler to remove that assumption, as was suggested:
If Target.Cells.Count > 1 Then Exit Sub
Presumably that event handler needs more tweaks to verify the
Target is what the macro thinks/assumes it is, but given the information you've provided that would be it.