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

[ammu]

Here is the code that I am using currently. Everything is working fine as expected, but there are two small things that need to be fixed.

Dim prevCell As Range
Dim PrevCell1 As Range


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet     '***
    Dim Cell As Range
    Dim rngInput As Range
    Dim rngTotal As Range
    Dim rngIntersect As Range
    Dim lgInputLastRowNum As Long

    Set ws = ActiveSheet    '***
    Set rngTotal = ws.Range("A:A").Find(what:="Total", LookAt:=xlPart, MatchCase:=False)    '***
    
    Set PrevCell1 = ws.Range("b" & Target.Row)
    
    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 = "Rakesh" Then
                With Cell.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=Datavalidationlist2"
                    MsgBox "Please select 'Student Code' from the dropdown list in cell B" & Target.Row, vbCritical
                    ws.Range("b" & Target.Row).Select
                End With

            ElseIf Target.Value = "Mukesh" Then
                With Cell.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=Datavalidationlist2"
                    MsgBox "Please select 'Student Code' from the dropdown list in cell B" & Target.Row, vbCritical
                    ws.Range("b" & Target.Row).Select
                End With

             ElseIf Target.Value = "Pallavi" Then
                With Cell.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=Datavalidationlist2"
                    MsgBox "Please select 'Student Code' from the dropdown list in cell B" & Target.Row, vbCritical
                    ws.Range("b" & Target.Row).Select
                End With

            ElseIf Target.Value = “Manju" Then
                With Cell.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=Datavalidationlist2"
                    MsgBox "Please select 'Student Code' from the dropdown list in cell B" & Target.Row, vbCritical
                    ws.Range("b" & Target.Row).Select
                End With

            ElseIf Target.Value = "Keerthi" Then
                With Cell.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Operator:=xlBetween, Formula1:="=Datavalidationlist2"
                    MsgBox "Please select 'Student Code' from the dropdown list in cell B" & Target.Row, vbCritical
                    ws.Range("b" & Target.Row).Select
                End With
            End If
        End If
    End If
    
    If rngTotal Is Nothing Then
        MsgBox "No TOTAL found. Exit Sub"
        Exit Sub
    End If


    lgInputLastRowNum = rngTotal.Row - 1

    Set rngInput = ws.Range("G16:K" & lgInputLastRowNum) '***

    If Target.Columns.Count = 1 Then    'ensure the "change" was not due to inserting row(s) *************
        Set prevCell = ws.Range("a" & Target.Row)   'moved this here *************************************
        Set rngIntersect = Intersect(Target, rngInput)
        
        If Not rngIntersect Is Nothing Then
        End If
    
    If Target.Value > 0 Then
        If ws.Range("a" & Target.Row).Value = "" Then
              MsgBox "Please select 'Student Name' from the dropdown list in cell A" & Target.Row, vbCritical
              ws.Range("a" & Target.Row).Select
        End If
    End If
    
    End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not prevCell Is Nothing Then
        If prevCell.Value = "" Then
            prevCell.Select
        End If
    End If
    
    If Not PrevCell1 Is Nothing Then
        If PrevCell1.Value = "" Then
            PrevCell1.Select
        End If
    End If
End Sub

1) If user enters data or value any where in the worksheet, it is asking user to select Column A value. It should ask only when the user enters a value in a cell range D8 to H last row only

2) also macro is selecting column B and forcing user to select Column B for all names which are there in column A dropdown list and not allowing user to select anywhere in the worksheet. In normal scenario, macro should select column B only when the user selects the names that were specified in criteria 3 in question no 1. Only in that case, macro should force the user to select Column B. Is this possible?