Hi
I have a spreadsheet that has a list of tasks with priorities ( 1,2,3,4,,,,etc) set in column A. I found below code that does the job but the priorities need to be in column B.
Basically When any of the numbers are changed in column B, the table is sorted automatically to move the rows to the correct position.
however, there are couple of issues:
- The macro debugs every time a row is deleted or column added or any other format changes to the priorities.
- There is a fixed range of data ( 300 rows) in the macro that preferably needs to be dynamic based on how many rows have data In them.
Would any of you smart people can modify this macro to rectify the issues listed above?
Happy New Year In advance.
Code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RangeOfInterest As Range, i As Long
Dim temp As String
Set RangeOfInterest = Range("A1:AA300")
If Not Application.Intersect(Target, RangeOfInterest.Columns(2)) Is Nothing Then
With RangeOfInterest
If .Row < Target.Row Then
Application.EnableEvents = False
Target.Value = Target.Value + Sgn(Target.Value - (Target.Row - .Row)) / 2
.Sort key1:=.Cells(1, 2), order1:=xlAscending, Header:=xlYes
.Columns(2).Offset(1, 0).Resize(.Rows.Count - 1, 1).Value = [Row(1:300)]
End If
End With
End If
Application.EnableEvents = True
End Sub
Display More
Thanks