Detrmining a range for a name

Hello everyone,

I need to create a range name in VBA based on specific value that I need to extract. For example, imagine the following example to create a range named "color":
A1: "blue" (the value that I need to extract)

and

B1:"green"
B2:"blue"
B3:"orange"
B4:"yellow"

I need to create a macro able to do something like

Please Login or Register  to view this content.

I thougt use a collection objet that would record green, orange and yellow, then copy paste each item of the collection in column C and then refer "color" to C1:C3... but I'm wondering if there is a faster way to obtain the same result.

Many thanks