Level Sort by Cell Color then Numerical Ascending

  • I've spent wayy too long searching for why my code isn't always working. The scenario is that I have a sheet (VGP) with Columns A:U. The number of rows is dynamic each time I use this. I need to sort the entire range by the cell color in Column M (RGB 255,0 ,0) so that they appear at the top of the sheet first. Then, I need Column L to sort the numerical values in ascending order. I have 2 bits of code in my macro that work with varying degrees of success. This first section works sometimes. Then doesn't. It will have Column L sorted in numerically ascending order, but Column M will be all over the place in whatever order Column L is in.

    So I turned to trying to sort by the cell color, because the cells that populate with "CG Trip Risk" in Column M based on the formulas in that column fill with a red background. I end up with a similar result, where it doesn't seem to be sorting by cell color first. The second section of code below is this attempt.

    The last section of code below contains the section above where the sort functions begin, where the formula is input that results in the "CG Trip Risk" string and the red background color. I've also included a sample file containing the sheet and data with how it looks after running the macro currently.

    I've searched all over the interwebs and tried dozens and dozens of different combinations with the same result, what am I missing?! Thank you!

  • Ohh thank you so much. This is so close, I just need some help adapting it to my scenario. There are red cells in various columns, but I only want to sort color based on Column M and then ascending number in Column L next. And rather than loop through all sheets (there are 5 total) I only want this specific sheet. I think I've done that with requiring the ws.name to be the correct sheet name before applying sort. Here's what I have so far.

    I'm planning to call this private sub from my main macro rather than integrate the code into my existing macro. Is that a good idea?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!