CODEWITHSUNDEEP
excelsortingworksheet-functionvba
Venkat
Venkat

Reputation: 47

Sort data by cell background color using VBA in Excel 2003

Thanks in advance.

Can someone help me in sorting the data using cell background color in Excel 2003.

I've 400 rows out of which 30 rows has Cell range A1 filled with background color yellow. I need to sort the data based on color in VBA to reduce the number of iterations I do on data ( If i can sort the data, the sorted data will be on top and I can only parse 30 rows instead of looping all 400 rows).

Upvotes: 1

Views: 1133

Answers (1)

Alexander Bell
Alexander Bell

Reputation: 7918

Starting with Excel 2007, you may perform this task in following steps: first, select the Worksheet Range, then click on Sort->Advanced, then in Dialog Box select "Sort On" option "Cell Color", and customize it pertinent to your task. You can use a Macro Recorder to generate the basic VBA code (include it in your post if you want us to optimize it).

Pertinent to Excel 2003, the possible solution will be using a helper function placed in the code Module:

' in Excel 2003 it could be Public by default if placed in code Module
Public Function BackgroundColorIndex(myRange As Range)
    BackgroundColorIndex = myRange.Interior.ColorIndex
End Function

then adding this function to some auxiliary Column and sorting on the values in that Column. This solution will also work in the later Excel versions (2007+).

Hope this will help.

Upvotes: 2

Related Questions