loop backwards for searching a string

Hi,

I want to run the loop backwards in order to search a string. The requirement is as below:

table.jpg

Please check the attachment for the sample data

I want to copy the rows from one sheet to another based on the string I am searching.
Lets say if the string I am searching from example above is "hat" in column C and there is no value corresponding to it in cell B9, then a loop should start to search for value in column B starting from B9 till the cell where it finds some value which in this case would be in cell B5 and value is "KUY".

I want to have the code for looping in reverse order.

Any help would be much appreciated.

Thanks,
Abhimanyu