sort by font format?

[Roni]

is it possible to sort Excel file rows based on the formatting. I have a long list of information, and some rows are in STRIKE THROUGH font (b/c they need to be removed). Is there a way I can sort the whole list having all the STRIKE THROUGH rows end up at the end (or the beginning)? and on the same note, can I also sort by font color? (in addition to the strike throughs, i also have a number of rows in RED FONT that would be nice to have all together).

 

[Andrew Poulsom]

Here's how to sort by colour:

http://www.cpearson.com/excel/SortByColor.htm

You would need another UDF for strikethrough.

 

[Roni]

doesn't seem to be working for me. I keep getting "NAME?" in the column where I've placed the sort color formula.

 

[Andrew Poulsom]

Where did you put the code? It needs to be in a General module in your workbook.

 

[erik.van.geit]

Hi,

perhaps retry, it should work

another approach, especially suggested when you are using large ranges
it is really very fast compared to Pearsons strategy

Sub sort_by_color()
'Erik Van Geit
'060625
'NateO pointed me to the idea

'bold               20
'italic             21
'underlined         22
'struck through     23
'font color         24
'outlined           25
'shadowed           26
'font style         58
'background color   63
'strikethrough      23
Const crit = 24
'column
Const col = 1


Application.ScreenUpdating = False
ThisWorkbook.Names.Add Name:="Color", RefersToR1C1:="=GET.CELL(" & crit & ",!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 Sub

kind regards,
Erik

 

[Roni]

i did put code inGENERAL module (right clicked on tab, VIEW CODE, new general module opens, i paste in code).

i've tried both codes, and i still get NAME?. i've even created new TEST files with assorted color fonts. are you sure the formula (=ColorIndexOfCell(A1,TRUE,TRUE) is correct? it seems I'm doing everything correct.

 

[Norie]

Roni

That isn't a general module, it's a worksheet class module.

You create a general module by going to Insert>Module.

 

[Roni]

i'm still having problems. one of the codes (can't remember if it was Erik's or the website one, i've tried to many time) worked once in a test file... but i can't get it to work again... is there a way to get rid of all the modules in an excel file and start from scratch?

 

[erik.van.geit]

getting rid of modules
rightclick on modulename: delete (export ? NO)

if you try my code, check:
1. the column: currently it will work on the first one
2. what it is looking at: currently it will check the font color

kind regards,
Erik

EDIT: and go for a walk

 

[Roni]

yes that's how i was deleting modules. but i can't tell if i got rid of all of them. Erik, if i use your code, do i still use the same formula in the online text (=ColorIndexOfCell(A1,TRUE,TRUE) and how can i tell if the code is checking font color? i want it to check font color.