When the dependent DV lists are not in CONTIGUOUS cells, your scenario is
messier to code, since there is no consistent relative cell relationship.
You'll most likely need an IF...ELSEIF...ELSEIF...ELSE....ENDIF construct
where you check the Target against the address of each of the DV cells. If
the Target matches a DV cell, then you'll need to clear the specifically
referenced next DV and prompt for input.

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Rishi" wrote:

Hi,

I got this code from this forum (Provided by Ron Coderre) to get Data
Validation list when selected show a msg box and move to the next DV list.
But the code work on contagious DV list i.e. A2, B2 and C2 etc. But I am
curious to know what modification is to be made in the Code if the DV list
are non contagious i.e. if i have DV in Cell A1 then B4 then C2 and A7.

Any suggestion will be appreciated,

Rishi


--------start of code----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim intCellCount As Integer

On Error GoTo ErrTrap

'Check if the active cell is one of the Data Validation cells
'But not the last one
If Not Intersect(Target, Range("A1:C1")) Is Nothing Then

'Count the DV cells to the right of the Active Cell
intCellCount = Range(ActiveCell, "C1").Cells.Count

'Select the cell to the right of the active cell
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=1).Select

'Turn off events so you don't get into and endless loop
Application.EnableEvents = False
With ActiveCell
'Clear the contents of the subsequent DV cells
.Resize(RowSize:=1, ColumnSize:=intCellCount) _
.ClearContents
End With
'Alert the user that an item must be selected
MsgBox _
Title:="Notice", _
Prompt:="You must now select an item from the next list", _
Buttons:=vbInformation + vbOKOnly
Application.SendKeys ("%{DOWN}")

End If

ErrTrap:
'Turn events back on...so Excel can behave normally
Application.EnableEvents = True
End Sub
'--------end of code----------