but column A is not necessarily as simple looking like that.

you might have to go hundred of rows down to come across a colour for the first time.
is it not just possible for a formula similar to your first one, but a bit shorter and simple.

if not, then i can make do with the initial formula.
thanks