cav~firez22
Well-known Member
- Joined
- Jun 21, 2006
- Messages
- 543
Is there a Macro, or formula that will sort a spread sheed by colour of cell?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Sort By Colored Cell?
- Thread starter cav~firez22
- Start date
onlyadrafter
Well-known Member
- Joined
- Aug 19, 2003
- Messages
- 5,703
- Platform
- Windows
Hello,
Not that I am aware of, but you could create some VB, whereby a column is created with the colour number in then sort by this column
Not that I am aware of, but you could create some VB, whereby a column is created with the colour number in then sort by this column
Upvote
0
cav~firez22
Well-known Member
- Joined
- Jun 21, 2006
- Messages
- 543
Ya my bad. found after i posted.. i kno i know. i should look first 
Upvote
0
erik.van.geit
MrExcel MVP
- Joined
- Feb 1, 2003
- Messages
- 17,832
Hi,
assuming you are NOT talking about conditional formatting
NateO once gave me this link
http://www.utteraccess.com/forums/showflat.php?Number=1147043&fpart=2.3
this should be faster then Chip Pearsons code, you will se the difference when used on large ranges
kind regards,
Erik
EDIT: added one dot in the code
With .Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count, col + 1).End(xlUp).Row, 1))
assuming you are NOT talking about conditional formatting
NateO once gave me this link
http://www.utteraccess.com/forums/showflat.php?Number=1147043&fpart=2.3
this should be faster then Chip Pearsons code, you will se the difference when used on large ranges
Code:
Option Explicit
Sub sort_by_color()
'Erik Van Geit
'060625
Const col = 3
Application.ScreenUpdating = False
ThisWorkbook.Names.Add Name:="Color", RefersToR1C1:="=GET.CELL(63,!RC[" & col & "])"
With Worksheets(1)
.Columns(1).Insert
With .Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count, col + 1).End(xlUp).Row, 1))
.Formula = "=Color"
.Resize(.Rows.Count, Columns.Count).Sort key1:=.Cells(1, 1)
End With
.Columns(1).Delete
End With
ThisWorkbook.Names("Color").Delete
Application.ScreenUpdating = True
End Subkind regards,
Erik
EDIT: added one dot in the code
With .Range(.Cells(1, 1), .Cells(.Cells(.Rows.Count, col + 1).End(xlUp).Row, 1))
Upvote
0
