Macro to select column to run 'sort by colours' macro on

[Sallyprit]

Hope one of you excel gurus can assist? I have recorded the following macro to sort column AE2 by cell colour. I now wish to be able to select any column to run the macro on, please can you advise? Many thanks indeed in advance

Sub SortingByColour()
'
' sortingbycolour Macro
' sorting for insights due within a month to due within 120 days
'

'
ActiveWorkbook.Worksheets("120 day insight").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("120 day insight").AutoFilter.Sort.SortFields.Add( _
Range("AE2"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
color = RGB(255, 192, 0)
ActiveWorkbook.Worksheets("120 day insight").AutoFilter.Sort.SortFields.Add( _
Range("AE2"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
color = RGB(255, 255, 0)
ActiveWorkbook.Worksheets("120 day insight").AutoFilter.Sort.SortFields.Add( _
Range("AE2"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
color = RGB(146, 208, 80)
ActiveWorkbook.Worksheets("120 day insight").AutoFilter.Sort.SortFields.Add( _
Range("AE2"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
color = RGB(253, 233, 217)
With ActiveWorkbook.Worksheets("120 day insight").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End With
End Sub

 

[Joe4]

If you select the cell you want to use before running the code, I think this modification to your original code should work:

Sub SortingByColour()
'
' sortingbycolour Macro
' sorting for insights due within a month to due within 120 days
'

'   Capture active cell to sort on
    Dim rng As Range
    Set rng = ActiveCell
    
'
    ActiveWorkbook.Worksheets("120 day insight").AutoFilter.Sort.SortFields.Clear
    
    ActiveWorkbook.Worksheets("120 day insight").AutoFilter.Sort.SortFields.Add( _
        rng, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(255, 192, 0)
    
    ActiveWorkbook.Worksheets("120 day insight").AutoFilter.Sort.SortFields.Add( _
        rng, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(255, 255, 0)
    
    ActiveWorkbook.Worksheets("120 day insight").AutoFilter.Sort.SortFields.Add( _
        rng, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(146, 208, 80)
    
    ActiveWorkbook.Worksheets("120 day insight").AutoFilter.Sort.SortFields.Add( _
        rng, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue. _
        Color = RGB(253, 233, 217)
    
    With ActiveWorkbook.Worksheets("120 day insight").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

 

[Sallyprit]

You legend! Thanks so much!

 

[Joe4]

You are welcome.
Glad I was able to help!