Sorting by Color

H

Héctor Miguel

hi, Manish !
Is it possible to sort in excel by color for entire row.
Click to expand...

IF you mean the 'real/complete' entire row...
some changes [minor I hope] has to be made.. this is 'the long way'...
[trying you to provide 'only' a specific range including titles which are not sorted]
the following macro will use the 'next' column for sorting numbered_colors...

hth,
hector.

Sub Color_Sorting()
Application.ScreenUpdating = False
Dim myRange As String, n_Cols As Byte, n_Rows As Long, _
fst_Cell As String, fst_Col As String, col_Sort As String, _
put_Formula_here As String, myFormula As String
myRange = "a1:f39" ' <= entire range here [including titles] '
n_Cols = Range(myRange).Columns.Count
n_Rows = Range(myRange).Rows.Count - 2
fst_Cell = Range(myRange).Cells(2, 1).Offset(, n_Cols).Address
put_Formula_here = _
Range(Range(fst_Cell), Range(fst_Cell).Offset(n_Rows)).Address
With Range(myRange)
fst_Col = Mid(.Address, 2, InStr(2, .Address, "$") - 2)
End With
With Range(fst_Cell)
col_Sort = Mid(.Address, 2, InStr(2, .Address, "$") - 2)
End With
Range(fst_Cell).Select
myFormula = _
"Get.Cell(13,!RC[-" & n_Cols & "])*1000+" & _
"Get.Cell(38,!RC[-" & n_Cols & "])+" & _
"Get.Cell(39,!RC[-" & n_Cols & "])/1000"
ActiveWorkbook.Names.Add _
Name:="mixedColors", _
RefersToR1C1:="=" & myFormula
ActiveCell.Formula = "=mixedColors"
ActiveCell.Copy
Range(put_Formula_here).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
Range(myRange).Resize(, n_Cols + 1).Sort _
Key1:=Columns(col_Sort), Order1:=xlDescending, _
Key2:=Columns(fst_Col), Order2:=xlAscending, _
Header:=xlYes
Range(put_Formula_here).Clear
ActiveWorkbook.Names("mixedColors").Delete
Range(myRange).Cells(1, 1).Select
ActiveSheet.UsedRange
End Sub
 
C

Chris

It's not possible to sort by color using the menu "Data | Sort...". You can
however write some code that can exploit the color's numerical value and use
that as a basis to do a color sort. You can use something to the effect of
"Activecell.Interior.Color" to get the numerical value of each cell.

Chris
 
Top