When I try to replicate what I think you mean above, mine works fine. I only get 01 for all colour codes if I enter a fruit on a row but do not allocate a colour to it. It's difficult to find the best way forward for that particular issue as I have no idea what the workbook will be used for or specifically HOW it will be used so I don't know if a product could be entered yet a colour not entered.

As for your edit, again without knowing how the workbook would be used, it's hard to second guess these things. For instance, is it highly likely that you would enter products and leave blank rows only to come back and fill them in later with more products?

If you need the colour codes added in the chronological order rather than a placement order then you will need to tackle this with a VBA approach rather than using a formula only approach.