Excel Expand Selection

Posted on 2011-05-05
Medium Priority
Last Modified: 2012-05-11
Hello Experts

Until reading a Excel and VBA for dummies I didn't think my next request was possible. However, it looks like almost anything is possible with Excel

I was wondering if someone could show me how to go about having Excel automatically A) sort a column by colour B) copy the selection and place the copied selection in a specified worksheet.

Let me explain.

You'll see from the attached workbook there are five worksheets. Each worksheet has a number of row. Some of the rows have been highlighted in column A3 for each sheet.

At the moment, I sorting the rows by colour and then copying the symbols with the highlights in column B and placing those symbols in the appropriate column in sheet MFI Green Scan Template.

If you look at sheet 'Four Lows Five Highv2' I've sorted the rows by colour then copied the symbols and pasted them in 'MFI Green Scan Template'.

I would like Excel to automate that procedure.

Can someone please tell me if it is actually possible and show me how to do it?


Question by:cpatte7372
  • 2
LVL 24

Expert Comment

ID: 35702171
Carlton - I cannot reconcile your description with your attachment. I cannot see any coloured rows in 'Four Lows Five Highv2', I'm not sure what you mean by symbols, and everything in 'MFI Green Scan Template' is just N/A errors. What am I missing?

Author Comment

ID: 35702247
Hi Stephen,

Thanks for responding. Sorry for the confusion. I meant coloured cells. I've just opened the sample spreadsheet and in 'Four Lows Five Highv2', cell A2:A16 are coloured. I Sorted that spreadsheet with colours to show you an example.

Does that make sense?

LVL 24

Expert Comment

ID: 35702316
How odd, I can't see any coloured cells, just a few on the next sheet. I hope I haven't gone temporarily colour blind. Perhaps somebody else will have more luck.
LVL 22

Accepted Solution

rspahitz earned 2000 total points
ID: 35710849
I've done something like thing a while back.
The way I handled it was to create a function that accepted a cell as an input and returned the code for the color of the cell.  From that, it then becomes easy to filter or sort based on the color's code.

In a module add:

Function GetColor(CellObject as Range) As Long
   GetColor= CellObject.Interior.Color
End Function

To use it, in a blank cell, just add this (for example):


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question