Excel Challenge: Macro based on cells background color - cut/paste and copy down

[lost_in_macros]

Hi awesome Mr. Excel community!

I am currently trying to create a rather complicated macro, and as you might have guessed by my user name, I am a bit lost right now.

This is how my data is currently shaped: I have one column which contains the data of owners (turquoise background, bgcolor=#CCFFFF or color=34 ) and tasks (white background). The number of owners, and the number of tasks per owner varies.

	A	B
1	Owner	Task
2		Anna (turquoise background)
4		Task 1 (white background)
5		Task 2 (white background)
6		Lisa (turquoise background)
7		Task 3 (white background)
8		Task 4 (white background)

<tbody>
</tbody>

This is how I want my data to look like:

	A	C
1	Owner	Task
2	Anna	Task 1
3	Anna	Task 2
4	Lisa	Task 3
5	Lisa	Task 4

<tbody>
</tbody>

What I want my Macro to do:

Cut all turquoise cells in column B and paste them into column A (two columns to the left). Then, starting from the first filled cell in column A, I want my macro to copy this cell into the empty cells in the rows below until it reaches the next filled cell. Then, I want it to go down one cell, copy this new cell and paste it into all the empty cells below until it gets to the next filled cell. I want this to happen until it reaches the end of the data (so some sort of if...then copy..until..then go down one..then loop construction). In the end, I would like every row with an empty cell in B to be deleted.

I know this is quite a complicated request, so all suggestions are welcome! Please reach out to me if you have any additional questions, and have a great day!

 

[lost_in_macros]

This worked PERFECTLY!!! Thank you so incredibly much!!

I have some more questions (not related to this thread): how did you learn all this? Is there certain recommended courses?

Thanks for your help!

 

[Rick Rothstein]

This worked PERFECTLY!!! Thank you so incredibly much!!

You are quite welcome... I am glad I was able to be of help.

I have some more questions (not related to this thread): how did you learn all this? Is there certain recommended courses?

I have been programming in various forms of BASIC (the predecessor to VB), the compiled version of VB (prior to the VB.NET version) and VBA since 1981 (not to mention a few other programming languages across the years), so most of what I know comes from experience. You may be able to find some reference and/or tutorial materials from the following rather comprehensive list compiled by a gentleman who posts as hiker95 (I believe it to be his most recent)...

http://www.mrexcel.com/forum/excel-...ing-range-into-concise-table.html#post3911064

 

[lost_in_macros]

Thank you very much, that will be very helpful! Have a great day and thanks once again!