Data Validation should be added when the user enter a value in a range of cells

Question

I know how to add a Data Validation list to a cell based on one cell value, but how to add Data Validation list to a cell when a value is entered in a range of cells? For example in the below image cell range is D8:H19 and this range is dymanic (Columns will not change, but number of rows will increse).

If the user enters a value in any of the cell (highlighted in yellow D8:H19) Data Validation List should be added in the same row Column A.

Can someone help me to with the code?

Here is the image enter image description here

IAmDranged IAmDranged · Accepted Answer · 2014-08-15T10:32:23

You could create a worksheet event procedure that would automatically execute any time the content of a cell or block of cells is changed in your worksheet to:

determine your dynamic range of reference
find the intersection between the cell or block of cells being changed and your range of reference
if this intersection actually returns a Range, then loop through each cell of this range and do your thing

Here is a framework that you could use - stick it in worksheet code module, and complete with code that does what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngInput As Range
    Dim rngTotal As Range
    Dim rngIntersect As Range
    Dim lgInputLastRowNum As Long

    Set rngTotal = Range("A:A").Find(what:="Total", LookAt:=xlPart, MatchCase:=False)

    If rngTotal Is Nothing Then
        MsgBox "No TOTAL found. Exit Sub"
        Exit Sub
    End If

    lgInputLastRowNum = rngTotal.row - 1

    Set rngInput = Range("D8:H" & lgInputLastRowNum)

    Set rngIntersect = Intersect(Target, rngInput)

    If Not rngIntersect Is Nothing Then
        For each cell in rngIntersect
            'Do your thing here
            'To select the cell in column A on the same row as your cell
            'do Range("A" & cell.row)
        Next cell
    End If

End Sub

Data Validation should be added when the user enter a value in a range of cells

3 Answers