Hello. Is there a way to sort by format? For example I have a spreadsheet with around 5,000 lines...some of which are highlighted in red. Is there a way to sort the highlighted lines to the top or bottom of the sheet? Thanks.
Sort by format (1 Viewer)
- Thread starter Evenflow
- Start date
Newman
Québécois
- Local time
- Today, 01:55
- Joined
- Aug 26, 2002
- Messages
- 766
A function that looks like this would add a cell with the number that represent the color of your cell. Then, you just have to sort that column.
(Note that this code isn't right. It is just a hint. You'll have to do some modification to make it work right.)
I hope this could be fixed for you by someone who have more time than I do.
(Note that this code isn't right. It is just a hint. You'll have to do some modification to make it work right.)
Code:
Public Function SortByColors()
Dim MyRange, Cell As Range
Set MyRange= Range("A1:A10")
For Each Cell In MyRange
Cell.Offset(0, 10).Value = Cell.Interior.ColorIndex
Next Cell
End functionI hope this could be fixed for you by someone who have more time than I do.
Brianwarnock
Retired
- Local time
- Today, 06:55
- Joined
- Jun 2, 2003
- Messages
- 12,701
Hi
I realise this is an old thread and you have probably solved your problem but if anybody else finds this via a search then they might like to try the following macro.
edit- Please note that a cells colourindex is not altered by conditional formatting.
Brian
I realise this is an old thread and you have probably solved your problem but if anybody else finds this via a search then they might like to try the following macro.
edit- Please note that a cells colourindex is not altered by conditional formatting.
Brian
Code:
Sub sortcolour()
'Brian Warnock December 2005
'Sort rows by colour index
Dim lnglastrow As Long
Dim lnglastcol As Long
Dim lnglastcol2 As Long
lnglastrow = ActiveSheet.UsedRange.Rows.Count
lnglastcol = ActiveSheet.UsedRange.Columns.Count
lnglastcol2 = lnglastcol + 1
For Each c In Range(Cells(1, lnglastcol), Cells(lnglastrow, lnglastcol))
c.Offset(0, 1).Value = c.Interior.ColorIndex
Next c
ActiveSheet.Range(Cells(1, 1), Cells(lnglastrow, lnglastcol2)).Sort _
key1:=ActiveSheet.Cells(1, lnglastcol2), order1:=xlDescending
ActiveSheet.Cells(1, lnglastcol2).EntireColumn.Delete
End Sub
Last edited:
