I'm probably not alone by being a little confused !
Do you want a VBA or a formula solution - could be done with either?
"Test each cell for negative value" - the cells in column A I presume. Correct?
"if true, b1=value of previous cell" - I presume because A3 is negative, and 5 is the number above it, B1 now=5. Correct?
What if there is more than one negative value in column A - or no negative value?