Excel Sort by Color with VBA

Learn Excel from MrExcel Podcast, Episode 2186: VBA Sort by Color.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question, sent in on YouTube. I had a video out there on how to sort with VBA, and they wanted to sort by color with VBA, which is much more complicated. I said, "Why don't you just turn on the macro recorder and see what happens?" And, unfortunately, the macro recorder, you know, it gets us close but it doesn't get us all the way there.

So View, Macros, Record Macro, "HowToSortByColor", Store Macro in This Workbook-- perfect. Click OK. Alright, so now the macro recorder is running, we're going to come here to the Data tab and we're going to say Sort. We're going to use a Sort dialog box and we're going to build this, alright? So, we're going to say that we want to add a level, Sort on Cherry, but not Sort on Cell Values; we're going to Sort on Cell Color-- Cell Color is the fill color there-- and we want to put red on top and then copy that level, and put yellow second; and then we'll add a new level-- we'll go to column D, the date column-- Sort on Cell Color, red first, copy that level, yellow, and then over here; an then, over here in Elderberry, column E, there's a few blue font I don't want to see what that looked like, so we'll add that as a Sort on Font color with blue on top; and then if all of these are a tie for no colors at all, we'll add one final level just on column A-- Cell Values, Largest to Smallest; and click OK.

Alright, now, a couple things-- don't skip this next step-- your file, right now, I guarantee you is stored as xlsx. This is a great time to do File, Save As, and save it as xlsm or xlsb. If you don't do that, all of your work to this point will be lost when you Save this file. They will delete the macros of anything stored in xlsx. Alright?

So we stopped recording there, and then we want to go look at our macros. So, you can do this with View, Macros-- View, Macros-- and find the macro we just recorded-- HowToSortByColor-- and click Edit. Alright, so here's our macro, and as I look at this, the problem that we have is, today we happen to have 25 rows plus a heading. So it goes down to row 26. And they've hard-coded that they're always going to look down to rows 26.

But as I think about this, especially compared to the old VBA for sorting, we don't have to specify the whole range-- just one cell in the column. So anywhere where they have column C26, I'm going to reduce it down to just say, "Hey, no, look at the first cell in that column." So E2, and then, here, A2. So in my case, I had 1, 2, 3, 4, 5, 6, sort levels-- 6 things to change.

And then this is the part that the macro recorder gets really, really bad, is they're only going to sort to rows 26 all the time. So I'm going to change this. I'm going to say, "Look, start at range A21, and extend it to  .CurrentRegion." Let's take a look at Excel and see what that does. So, if I would just choose any one cell-- A1 or anything-- and press Ctrl+*, it selects the current Region. Okay, let's do it. Here, from the middle, Ctrl+*, and what that does, is it extends in all directions till it hits the edge of the spreadsheet, on top of the spreadsheet, or at the right edge of the data or the bottom edge of the data. So, by saying A1 .CurrentRegion, it's like going to A1 and pressing Ctrl+*. Alright? So, here you have to change that thing. Now everything else in the macro is fine; it's all going to work. They got the SortOnCellColor and SortOnFontColor and xlSortOn. I don't have to worry about any of that; all I have to do is look in here and see that they hard-coded the region they were going to use for the range, hard-coded how far those went, and it doesn't need to be hard-coded. And with that simple step, changing those six items and the seventh item, we have something that should work.

Now, let's do the test. Let's come back here to Excel and we will add some new rows at the bottom. I'll just put 11s there, and we will add a couple of Reds--a red, a yellow, and then over here a blue. Alright. So if we go run this code-- run this code, so I click in here, and click the Run button-- and then come back, we should see that that 11 became the top item in red, it showed up there in the yellows, and it shows up in the blues, so that is all working perfectly. Why did it go to the top? Because it happened that the last sort is Column A and so when there's a tie, it looks to column A as the tiebreaker. So that code is working.

To learn how to write VBA, I, along with Tracy Syrstad, have written a series of books, Excel VBA and MACROS. There's been an edition now for 2003, 2007, 2010, 2013 and 2016; soon 2019. Alright, so, go find the version that matches your version of Excel and this will get you up the learning curve.

Wrap-up: Today's Episode is, How to Use VBA to Sort by Color. The easiest way to do this, especially since you don't know what RGB codes were used for each of the colors-- you just chose red you don't know what the RGB code is, and you don't want to go look it up-- turn on the macro recorder using View, Macros, Record New Macro. After you're done doing the sort, click Stop Recording-- it's in the lower left-hand corner-- Alt+F8 to see a list of macros, or View, Macros, View Macro-- the View tab, Macros, and then View Macros-- that's confusing. PSelect your macro and click Edit, and anytime you see C2 to some range numbers, just change it to point to row 2. And then, where they specify the range to sort, Range("A1"),CurrentRegion, will expand. Alright.

Well, hey, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.