sort by font format?

Roni

Board Regular
Joined
Apr 2, 2002
Messages
231
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).
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Roni

Board Regular
Joined
Apr 2, 2002
Messages
231
doesn't seem to be working for me. I keep getting "NAME?" in the column where I've placed the sort color formula.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

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
Code:
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

Board Regular
Joined
Apr 2, 2002
Messages
231
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Roni

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

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

Roni

Board Regular
Joined
Apr 2, 2002
Messages
231
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Apr 2, 2002
Messages
231
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.
 

Forum statistics

Threads
1,141,096
Messages
5,704,312
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top