HI Gary

Considering that I have different sets of Colors (In the Colors Sheet), I
use the following formula to define which is element that corresponds to it:

=OFFSET(INDIRECT(SUBSTITUTE($G15,"
","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($G15," ","")&"_Col")),1)

That formula is taken from the following example to make dynamic lists:
http://www.contextures.com/xlDataVal02.html


Would be possible to reproduce that formula in a VB macro or do you think
that there is a better approach to it ?


Thanks.

"Gary''s Student" wrote:

Lets assume that the validation list for column C is H1 thru H3 (containing
the colors: red, green, blue). Then:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a = Range("A15:A12000")
If Intersect(t, a) Is Nothing Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 2).Value = Range("H1").Value
Application.EnableEvents = True
End Sub

--
Gary''s Student - gsnu200858


"DimensionAV" wrote:

HI

I have a couple of range names ProductQuantity and ProductColor (A15:A12000
and C15:12000 respectively), ProductColor are data valitaded cells with a
list of colors, I want that after entering a value to ProductQuantity the
corresponding row of ProductColor shows the first value of the list.

I belive a have to use Worksheet_Change event but how to get/set the value
on a datalist cell ?

Thanks in advance.