Excel Expand Selection

Posted on 2011-05-05
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
    LVL 24

    Expert Comment

    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

    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

    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

    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 Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now