Macro to check if current cell value is 1 and previous cell was 0

[wissam]

Hi.

I have a range of cells (C42:M42) where the user can enter values of 0,1,or
2 only (set up via data validation).

I need help with a code to do the following: if the worksheet user enters a
value or changes the value in a cell in the above range (C42:M42), then excel
would automatically check ( via a macro?) the value entered and compare this
value to the value present in the previous cell on the same row. If the value
entered is 1 or 2, and the value in the previous cell was 0, then it would
give a msgbox alert.

Any help is appreciated.

Thank you very much.

 

[Mike H]

Hi,

I think i've understood the question , try this. Right click your sheet tab,
view code and paste this code in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("C1:M41")) Is Nothing Then
If Target.Value = 1 Or Target.Value = 2 And _
Target.Offset(, -1).Value <> "" _
And Target.Offset(, -1).Value = 0 Then
MsgBox ("You entered " & Target.Value & " After a zero")
End If
End If
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

Give this a try. Right click the tab at the bottom of the worksheet you want
to have this functionality, select View Code from the popup menu that
appears and copy/paste the following into the code window that appeared
(change the MsgBox text argument to say what you want for the alert)...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C42:M42")) Is Nothing Then
If (Target.Value = 1 Or Target.Value = 2) And _
Len(Target.Offset(0, -1).Value) > 0 And _
Target.Offset(0, -1).Value = 0 Then
Target.Select
MsgBox "Put whatever alert message you want here"
End If
End If
End Sub

Note: This code only does what you asked, but I wonder, did you need an
alert message if there was a 1 or 2 in a cell (in the specified range) and
the user entered a 0 in the cell before it?

 

[Ryan H]

Put this code in the worksheet module. This code will only run if a cell
value is changed in C42:M42. It will only show an alert if the Target cell
value is 1 or 2 and the cell to the left of it is 0. Hope this helps! If
so, let me know, click "YES" below.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range

Set MyRange = Intersect(Target, Range("C42:M42"))

If Not MyRange Is Nothing Then
With Target
If (.Value = 1 Or .Value = 2) And .Offset(, -1).Value = 0 Then
MsgBox "ALERT"
End If
End With
End If

End Sub

 

[wissam]

Thank you very much.
It worked. The only minor thing I did is change as snippet to the following:
(Target.Value = 1 Or Target.Value = 2) And _
(Target.Offset(, -1).Value <> "" _
And Target.Offset(, -1).Value = 0)
because it was giving the alert when the cell to the left was blank before
this change.
Thanks again.
Wissam

 

[wissam]

Thank you very much.
It worked.

Give this a try. Right click the tab at the bottom of the worksheet you want
to have this functionality, select View Code from the popup menu that
appears and copy/paste the following into the code window that appeared
(change the MsgBox text argument to say what you want for the alert)...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C42:M42")) Is Nothing Then
If (Target.Value = 1 Or Target.Value = 2) And _
Len(Target.Offset(0, -1).Value) > 0 And _
Target.Offset(0, -1).Value = 0 Then
Target.Select
MsgBox "Put whatever alert message you want here"
End If
End If
End Sub

Note: This code only does what you asked, but I wonder, did you need an
alert message if there was a 1 or 2 in a cell (in the specified range) and
the user entered a 0 in the cell before it?

--
Rick (MVP - Excel)




.

 

[wissam]

Thank you.
It worked.

Put this code in the worksheet module. This code will only run if a cell
value is changed in C42:M42. It will only show an alert if the Target cell
value is 1 or 2 and the cell to the left of it is 0. Hope this helps! If
so, let me know, click "YES" below.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyRange As Range

Set MyRange = Intersect(Target, Range("C42:M42"))

If Not MyRange Is Nothing Then
With Target
If (.Value = 1 Or .Value = 2) And .Offset(, -1).Value = 0 Then
MsgBox "ALERT"
End If
End With
End If

End Sub