Excel Expand Selection

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?

Cheers

Carlton
ExpandSelection.xlsm
cpatte7372Asked:
Who is Participating?
 
rspahitzCommented:
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):

=GetColor(A1)
0
 
StephenJRCommented:
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?
0
 
cpatte7372Author Commented:
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?

Cheers
0
 
StephenJRCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.