Excel 2007 : Copy data from a cell based on the colour of the cell and paste into another cell

Hi - I'm new to this forum and to Excel macros in general so apologies if my problem is not clear and forum etiquette poor.

Basically what I want to do is automatically search the range M3:X50 for the colour orange and copy the data contained in these orange cells into the column Y (from Y3 down) using a macros or other excel function.

I have attached a sample spreadsheet to illustrate what I want to do.

Thank you in advance.

David

Color cell Y1 to whatever color you want to collect from the range of grades, then click the button to collect them. Here's the code:

Option Explicit

Sub MatchColor()
Dim MyColor As Long
Dim LastRow As Long
Dim MyRange As Range
Dim cell    As Range

MyColor = Range("Y1").Interior.ColorIndex
LastRow = Range("L" & Rows.Count).End(xlUp).Row

Set MyRange = Range("M3:T" & LastRow)
Range("Y3:Y" & LastRow).Clear

    For Each cell In MyRange
        If cell.Interior.ColorIndex = MyColor Then _
            cell.Copy Range("Y" & cell.Row)
    Next cell

Set MyRange = Nothing
End Sub

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.