Code is missing Something

[farr80]

I have a sheet that I am trying to run a macro when the value changes in any cell of one column only. When a cell value does change, it will run the macro. Problem is that if any cell changes it runs the macro. Adding rows has become a pain because every cell runs the code and sends me to the top of my sheet. I would be happy to email the file as I do not see a way to attach it in here. Below is my current code. I only want the macro to run when any cell value from H3 down changes. The macro is nothing more than to sort Column H by red fill first , then column A by A-Z. My frustration is 2 weeks old now so any help would be appreciated as I am a beginner at best with this type of thing.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rangeTochange As Range

Set rangeTochange = Range("A3:I100")

'Run the code when a cell within a range is changed.
If Not Intersect(Target, rangeTochange) Is Nothing Then
ActiveWorkbook.Worksheets("X5 Parts").ListObjects("Table2").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("X5 Parts").ListObjects("Table2").Sort.SortFields.Add _
(Range("Table2[Need Ordered]"), xlSortOnCellColor, xlAscending, , xlSortNormal) _
.SortOnValue.Color = RGB(255, 0, 0)
ActiveWorkbook.Worksheets("X5 Parts").ListObjects("Table2").Sort.SortFields. _
Add2 Key:=Range("Table2[Part Number]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("X5 Parts").ListObjects("Table2").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If

End Sub

 

[Eric W]

First, add this line at the top of your macro:

If Target.Cells.Count > 1 Then Exit Sub

This will prevent the macro from running when you add a row. Next, change this line:

Set rangeTochange = Range("A3:I100")

to

Set rangeTochange = Range("H3:H1000")

which will mean only values in the H3:H1000 range will trigger the macro.

 

[farr80]

First, add this line at the top of your macro:

If Target.Cells.Count > 1 Then Exit Sub

This will prevent the macro from running when you add a row. Next, change this line:

Set rangeTochange = Range("A3:I100")

to

Set rangeTochange = Range("H3:H1000")

which will mean only values in the H3:H1000 range will trigger the macro.

That was unsuccessful. Only the H cell turned red as as needed but did not sort to the top. I actually changed had changed the range before to your suggestion but it did the exact same thing. I wish i could upload the darn thing, visual is always key for me.