VBA to find red font, then find black font & paste that value next to the red font

I have a list of serial numbers for farm and construction machines. Some serial numbers are black and some are red. The black indicate a parent machine and the red indicate an attached machine. (Note: There can be several attachments on 1 parent machine.)

Example:
456789 (tractor SN)
987312 (loader SN)


I want to have a macro that would find any cell where the font is red (an attachment). Then it would look at the previous cells until it found one that had black font (the parent). It would copy the serial number of the parent and paste it in the cell just to the right of the attachment. So it would end up looking like this (if this was a spreadsheet):

456789 (tractor SN)
987312 (loader SN) 456789 (tractor SN)


Any ideas?

UPDATE:
I was able to get this code to work, but it only looks at the 2 previous cells to see if the font is black or not. I would need it to continue looking until it got to black font...

Please Login or Register  to view this content.