VBA for Sorting Rows

Hi all,

have come across this forum as been looking for VBA for a spreadsheet I have created.

The table has 3 columns A- description B- status and C- date status updated. I have created a drop down in column B and have stored a VBA so that when one of 6 drop downs is selected, the row will change colour. Now what I am looking to do is sort the rows automatically so that when a drop down is selected the whole row is moved with other rows of that status and then by date order in that status. I have created my own sort as I want them to stay in order and not go into alphabetical order.

Could anybody help with this please? I have attached a copy of the spreadsheet so far.

Something else I am possibly looking to do (although may be getting too complicated) is add a final drop down 'completed' and when a row is changed to this, I would like the row removed and placed onto the 2nd worksheet which shows completed jobs.

Thanks in advance. Hope I explained myself ok

Dan

You have explained your requirement very well. However, I don't quite understand why you use a worksheet change event to colour the rows ... why not use simple conditional formatting?

If you do feel the need to use VBA, I would restrict the range that is being checked to just column B. Otherwise, if you edit something in column A, the colour will change.

And, as you already have the worksheet change event, you could just tag a call to your sort code onto the end of that. But ... your users might find it confusing if the row they have changed moves somewhere else on the worksheet ... particularly if there are a lot of rows of data.

Regards

Hi, I couldn't use basic conditional formatting as there are more than 4 colours.
Thanks very much for your help Leith, its looking much better! Only thing is, that I would each status to stay in the same order it is in the drop down, rather than move to the top when it is selected. So starting with all the 'Awaiting CR Review' at the top and ending with all the 'Awaiting Implemenation' at the bottom, with each status staying in date order.

The second thing I was speaking of, was adding a 7th drop down to the list being 'Completed' and when this was selected the row could then be removed from the outstanding spreadsheet and the decription added to the completed work sheet which is the 2nd tab. The dates on the completed worksheet could be completed manually but this would save jobs being missed off, or having to copy and paste data across. Is that possible?

If you are using Excel 2007 or above, I believe the number of conditions is limited only by memory.

A little less code and a little more external flexibility.

See attached screenshot.

Regards

Sorry, I am probably becoming a bit of a pain now with this! Unfortunately that macro to sort the list does not seem to work quite as it should for some reason and I cannot see why. The order it is placing the list in is: Awaiting CR review, Development, System testing, User testing, Awaiting Implementation, awaiting Auth and awaiting est - rather than the order of the drop down. Any ideas on why this is?