IF Statement For Active Row (worksheet_change)

Question

I have an Excel template with an table where I calculate a couple of recommended values (Column I, J & K). Then the user selects the value they want from an data validation dropdown-list in (Column L).

If the user selects a value that are <= 6 units from the min recommended value I would like to show a message box just telling that "your selected value are low", and just continue without action.

And if the user selects a value that are < 3 units from the min recommended value I would to show a message box telling that "your selected value are to low, select another value" and clear the active cell where they select their values.

Column I = Recommendation
Column J = Deviation from the recommendation <= 6
Column K = Deviation from the recommendation < 3
Column L = User selection

So dream scenario would be:

User selects a value that are > 6 units from recommendation, do nothing
User selects a value that are <= 6 units from the recommendation, reminder that they are close

User selects a value that are < 3 units from the recommendation, clear the users selection and tell them to select a greater value.

Private Sub worksheet_change(ByVal target As Integer) Dim target As Integer

For target = 2 To 1000
    If Cells(target, 12).Value <= Cells(target, 9).Value Then
    MsgBox ("Your selection are close to the recomandation")
        ElseIf Cells(target, 12).Value < Cells(target, 11).Value Then
        MsgBox ("Your selection are to low, pick a higher value")
        ActiveCell.Clear
            Else: exitsub
            End If

End Sub

user3523992 user3523992 · Accepted Answer · 2019-12-12T09:31:53

Solved the main question with below code:

But still struggling with top row, if activecell = row 1 then exitsub.

Private Sub Worksheet_Change(ByVal Target As Range)

If ActiveCell.Column <> 11 Then
    Exit Sub
        Else
If ActiveCell.Offset(-1, -1).Value > 0 And ActiveCell.Offset(-1, -1).Value <= 3 Then
    MsgBox "Your selected value are low", vbInformation
     Exit Sub
ElseIf ActiveCell.Offset(-1, -1).Value >= 3.1 Then
    MsgBox "your selected value are to low." & vbCrLf & "The value will automatically be set to minimum.", vbCritical
        ActiveCell.Offset(-1, 0).Value = ActiveCell.Offset(-1, -2).Value
End If
End If

End Sub

IF Statement For Active Row (worksheet_change)

1 Answers