With Data like this in sheet2:-
[TABLE="width: 400"]
<tbody>[TR]
[TD="class: xl66, width: 27, bgcolor: #DAEEF3"][/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]
A
[/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]
B
[/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]
C
[/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]
D
[/TD]
[TD="class: xl66, width: 101, bgcolor: #DAEEF3"]
E
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DAEEF3, align: right"]
1
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]
Cars
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]
Colour 1
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]
Colour 2
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]
Colour 3
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]
Colour 4
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DAEEF3, align: right"]
2
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]
VW
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]
Blue
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]
Green
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]
Red
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DAEEF3, align: right"]
3
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]
Ford
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"]
Yellow
[/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"][/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"][/TD]
[TD="class: xl67, width: 101, bgcolor: #DCE6F1"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: #DAEEF3, align: right"]
4
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]
Vauxhall
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]
Red
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]
Purple
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]
Blue
[/TD]
[TD="class: xl65, width: 101, bgcolor: white"]
Pink
[/TD]
[/TR]
</tbody>[/TABLE]
Place this code in your "Validation list" Sheet
The code will Insert the validation in "A1" when you Select that "A1"l, and in "B1" when you Select a Make of Car from "A1".
Code:
Private [COLOR=navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] ray
[COLOR=navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=navy]Then[/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Sheet2")
[COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
[COLOR=navy]With[/COLOR] Range("A1").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(Application.Transpose(Rng), ",")
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, nstr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] nRng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=navy]Then[/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Sheet2")
[COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
[COLOR=navy]If[/COLOR] Dn.Value = Target.Value [COLOR=navy]Then[/COLOR]
[COLOR=navy]Set[/COLOR] nRng = .Range(.Cells(Dn.Row, 2), .Cells(Dn.Row, Columns.Count).End(xlToLeft))
[COLOR=navy]If[/COLOR] nRng.Count = 1 [COLOR=navy]Then[/COLOR]
nstr = nRng.Value
[COLOR=navy]Else[/COLOR]
nstr = Join(Application.Transpose(Application.Transpose(nRng)), ",")
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Exit[/COLOR] For
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] With
Range("B1").Value = ""
[COLOR=navy]With[/COLOR] Range("B1").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=nstr
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
code