HELP Getting data to final sheet based on highlighted cells!!!

Hello,
In Sheet1, I have rows 3,4, and 5 that contain cells that are highlighted. The highlighted cells after column A and B show that the data is bad and should not be put in final sheet. Each highlighted cell is in a column and other named ranges corresponding to that same column will not go in the final sheet.

I want to make a code that will pick only the columns in the named range set that corresponds to none of the highlighted cells ( I can have all of the named ranges pulled over even the ones in Sheet2 EXCEPT the named range called "POP") to bring to the final sheet called "Final_504", taking their data in column A and B along with them.

I made the outcome manually in the final sheet to let you see what I am saying but I need to code it in a generic way. So if I was to do this with 500 data points, I could.

Can anyone help me with this? :(

See button in attached on Sheet1 near cell S1.
Depends on your named ranges being properly set up (they don't all have to start in column A).
Doesn't clear the destination sheet before writing to it.

p45cal,
For c = 3 To ... loop can be shortened to form:

Code:

---

    For c = 3 To RngColoured.Columns.Count
        If RngColoured.Columns(c).Interior.Color <> 0 Then
            For Each rng In RangesToCopyFrom
                rng.Columns(c).Copy Destn
                Set Destn = Destn.Offset(rng.Rows.Count + 1)    'A1
            Next rng
            Set Destn = Destn.Parent.Cells(1, Destn.Column + 1)
        End If
    Next c

---

The Interior.Color property for a range with cells of different colors returns 0.

Artik

Quote:

---

Originally Posted by Artik

The Interior.Color property for a range with cells of different colors returns 0.

---

Yes, that does shorten it considerably and it will work in this case.
BTW, if all the cells are black, that too will return 0, but it doesn't matter here.

It worked!!!!!!!!!!!!!!!!!!!!!!
Thank you guys!!!!!