Macro to sort Worksheet by cell reference text

[mayhem]

Dear Chandoo you genius you, pls advise if possible to sort multiple worksheets by a cell reference text using a Macro. I am also curious to know if possible for cell reference by colour. Thank you

 

[Hui]

Mayhem


Firstly, Welcome to the Chandoo.org Forums


You may want to try the following VBA code:

[pre]

Sub SortSheets()
Dim lCount As Long, lCounted As Long
Dim lShtLast As Long
Dim lReply As Long
Dim SortCell As String
SortCell = "$A$1" 'Change address to suit your application

lReply = MsgBox("To sort Worksheets in Ascending order, according to Cell " + SortCell + _
", Select 'Yes'. " + Chr(13) + "To sort Worksheets in Descending order select 'No'", vbYesNoCancel, "Ozgrid Sheet Sort")

If lReply = vbCancel Then Exit Sub

lShtLast = Sheets.Count
If lReply = vbYes Then 'Sort ascending
For lCount = 1 To lShtLast
For lCount2 = lCount To lShtLast
If UCase(Sheets(lCount2).Range(SortCell).Value) < UCase(Sheets(lCount).Range(SortCell).Value) Then
Sheets(lCount2).Move Before:=Sheets(lCount)
End If
Next lCount2
Next lCount
Else 'Sort descending
For lCount = 1 To lShtLast
For lCount2 = lCount To lShtLast
If UCase(Sheets(lCount2).Range(SortCell).Value) > UCase(Sheets(lCount).Range(SortCell).Value) Then
Sheets(lCount2).Move Before:=Sheets(lCount)
End If
Next lCount2
Next lCount
End If

End Sub

[/pre]

Code was adapted from: http://www.ozgrid.com/VBA/sort-sheets.htm

 

[mayhem]

Thank you so much Guru Chandoo, I am overwhelmed with gratitude, it came in at the right hour

 

[mayhem]

now how would I amend this should the cell reference be a colour. Is that possible?