recognizing colored cells in a list and copying to the side

I'm trying to solve this Excel puzzle. What I'm looking to do is recognize project names in a list, and assign them to the roles also in the list. There are a few elements to this puzzle:

1) Recognizing the project names as such. They are colored differently but that is the only difference. They don't change too often so it would be possible to refer to a static list of them, but I'd have to update that list. I'm told that it takes VBA to recognize cell colors.

2) Moving the project names to the relevant spots in another column.

I get the output from some software in the format of the first tab in the attached file. I'd like to make it look like the second tab so that I can make the pivot table shown on the third tab.

As for my Excel experience, I'm pretty good with Excel but new to macros/VBA. Thanks for any advice you can give!

Sample File.xlsx

Thanks, jkj115! This works great. Now just let me see if I can use my macro knowledge to apply it to my real document, which is a little more complicated. I may have some follow-up questions

What do I need to do to make the macro stop if it reaches a cell of ColorIndex=48?

Aha! I got it. To make the macro stop I added

Please Login or Register  to view this content.