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.[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Owner[/TD]
[TD]Task[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]
[/TD]
[TD]Anna (turquoise background)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]
[/TD]
[TD]Task 1 (white background)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]
[/TD]
[TD]Task 2 (white background)[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]
[/TD]
[TD]Lisa (turquoise background)[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]
[/TD]
[TD]Task 3 (white background)[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]
[/TD]
[TD]Task 4 (white background)[/TD]
[/TR]
</tbody>[/TABLE]


This is how I want my data to look like:

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]
[/TD]
[TD]A[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Owner[/TD]
[TD]Task[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Anna[/TD]
[TD]Task 1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Anna[/TD]
[TD]Task 2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Lisa[/TD]
[TD]Task 3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Lisa[/TD]
[TD]Task 4[/TD]
[/TR]
</tbody>[/TABLE]


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!