Sort in Excel based on format

[cjjn]

Hi,
I have checked the help files - maybe i'm not asking the right way. I cant seem to find a way to sort based on cell formatting.
I have conditional formatting in place - if Column A=ColumnB the Color is red if A>B color is Blue etc.
I want to be able to sort by that condition - am I making sense?

Thank you!

 

[st24v6]

Normally I would say that you cannot "sort" by colors but I found a magical piece of kit last week called DigDB (I am in now way affiliated with this company nor do I endorse it)
This add in allows you much more powerful sort functions and I think is a great tool for anyone doing analysis in Excel.
Again, just my thoughts and in no way an endorsement
Have a look yourself and see what you think, I am not posting their website here as I don't think it appropriate (nor allowed) but have a look on a popular search engine for them.


PJ

"If you don't know how, ask 1st"

 

[GlennUK]

Have an extra column, and put in formulae that do the same kind of logic as your conditional formatting. For example ...

=IF(A2=B2,1,IF(A2>B2,2,IF(other_condition,3,999)))

Then sort on that column.

Cheers, Glenn.

 

[cjjn]

Thank you both!

 

[mscallisto]

You didn't mention if the fonts or background were colored so I offer an example of sort by font color:

Sort by Font Colors
(an example with 13 rows the colored fonts in column a the color font index number in column b):

Sub colorval()
for ii - 1 to 13
Rows(ii).Columns(2)= Rows(ii).Columns(1).Font.Colorindex
next ii
Endsub


Where:
If the fonts of rows 1 to 4 in col A are black
and the fonts of rows 5 to 7 in col A are dark red
and the fonts of rows 8 to 13 in col A are dark blue
The corresponding rows in col B will have the values of their color index.

You can then sort by Col B then Col A

The good part is you can insert and delete rows and not upset the applecart.


Example of column B color values after running macro
A B
1 aaa 1
2 bbb 1
3 ccc 1
4 1
5 ddd 9
6 ddd 9
7 9
8 eee 11
9 eee 11
10 eee 11
11 eee 11
12 eee 11
13 11

I never can get these examples to line up!
(You could Hide Column B or use some far right Column)