CODEWITHSUNDEEP
vbaexcel
vbalearner
vbalearner

Reputation: 133

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

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

Upvotes: 0

Views: 117

Answers (3)

IAmDranged
IAmDranged

Reputation: 3020

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

Upvotes: 1

publicist
publicist

Reputation: 17

Validation.Add Method is used for validating data using vba

Upvotes: 1

StorymasterQ
StorymasterQ

Reputation: 262

Asked many times in StackExchange, actually. Try this Dynamic range.

Upvotes: 1

Related Questions