How can I edit this sort so it work when a set of cells is clicked not just1?

[tonywatsonhelp]

Hi Everyone

The code below works great,
what it does is when I click on cell AJ101
it sorts my data using AJ as the sort column.

Now I want to be able to do this with all the headers in the range, so when I click AC101 it sort by AC ect using the same rules as I currently use.

So how can I change this so it says If target is within Range AB101:AO101 (instead of (If Target.Address = "$AJ$101" Then))
Then sort
and
SortCol ="selected cell" instead of (SortCol = "AJ") ??

any ideas?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$AJ$101" Then
Sheets("Dashboard1").Unprotect
SortCol = "AJ"
Lrow = Sheets("Dashboard1").Cells(Rows.Count, "AC").End(xlUp).Row
If Lrow < 102 Then
Lrow = 102
End If
Sheets("Dashboard1").Range("AB101", "AO" & Lrow).Sort Key1:=Range(SortCol & "102", SortCol & Lrow), Header:=xlYes, Order1:=xlDescending
Sheets("Dashboard1").Protect
Exit Sub
End If

Thanks

Tony

 

[dmt32]

Hi,
not tested but have a play with this updated code & see if helps you

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim SortCol As String
    Dim Lrow As Long
    
    If Not Intersect(Target, Me.Range("AB101:AO101")) Is Nothing Then
        Me.Unprotect
        SortCol = Left(Target.Address(0, 0), 2)
        Lrow = Me.Cells(Rows.Count, "AC").End(xlUp).Row
        If Lrow < 102 Then Lrow = 102
        
        Me.Range("AB101", "AO" & Lrow).Sort Key1:=Range(SortCol & 102, SortCol & Lrow), Header:=xlYes, Order1:=xlDescending
        Me.Protect
    End If
End Sub

I have assumed that Sheets("Dashboard1") is the activesheet.

Hope Helpful

Dave

 

[tonywatsonhelp]

Brilliant Dave,
thanks so much

this is exactly what I needed

Tony

 

[dmt32]

Brilliant Dave,
thanks so much

this is exactly what I needed

Tony

Welcome - thanks for feedback

Dave