Need additional coding help

[hshayh0rn]

I'm looking for someone who can expand on that I already have here.

http://www.microsoft.com/office/com...e6762d-b1ce-4ae8-993d-54620e3e5286&sloc=en-us

I'm looking to take the above thread a little further. Right now based on a
value in a cell on sheet 1 the entire row is copied to sheet 2. The problem
is the copied row may have a blank value in column A. Column A has values at
the start of each list and then nothing until a new list is started. I need
the code that was detailed in the above link to look in column A upwards
until it finds a value and then use that value.

Also, the copied column is colored. However, when it's pasted into sheet 2
there is an addiitonal column at the end of the row and obviously that column
wouldn't have the same color as the rest of the row. I need the code to
determine the color of the row and make that column's cell the same color.
For example:

row 3 is copied to sheet 2. Column G is the added column on sheet 2 so we
need to color cell G3 the same color as the row we just copied.

Can anyone help me with this?