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

[ammu]

Dear All,

Currently I am using the following macro which is working fine only for one column.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    If Target.Column = 1 Then
        Set Cell = Target.Offset(0, 1)
        If Len(Target.Value) = 0 Then
            Cell.Validation.Delete
            Cell.Value = vbNullString
        Else
            If Target.Value <> " " Then
                With Cell.Vaidation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=DatavalidationList1"
                End With
            ElseIf Target.Value = "" Then
                Cell.Validation.Delete
            Else
                MsgBox "Select only from the DropDown list"
                Target.ClearContents
                Cell.Validation.Delete
            End If
        End If
    End If
End Sub

I am attaching a sample workbook for your reference.

1) But now I have one more new scenario where I already have data validation in column A,but if user enters any value which is >0 in any of the columns D to H, then for that particular row cursor should go to column A and there should be a text message in RED saying "Please select from the drop down list..." and until user selects any one from the drop down list, cursor should not allow him/her to move anywhere in the worksheet (all other options should be disabled). User should not have a choice, but to select from the dropdown list.

2) Currently we have rows from 8 to 39, hence user may insert N number of rows after filling all 8 to 39 rows, then the above functions should work even for rows that were inserted newly. This should be dynamic

3) Also only for the following students "Rakesh, Mukesh, Pallavi, Manju and Keerthi" if we select their name in Column A, then in the same row column B, data validation list2 should be added and a text message in RED should appear saying "Please select from the drop down list..." and until user selects any one from the drop down list, cursor should not allow him/her to move anywhere in the worksheet (all other options should be disabled). User should not have a choice, but to select from the dropdown list.

Can someone please help me with this macro?


Best Regards
Amrutha