Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to check if current cell value is 1 and previous cell was 0
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to check if current cell value is 1 and previous cell was 0
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. "wissam" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to check if current cell value is 1 and previous cell was 0
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to check if current cell value is 1 and previous cell was 0
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 -- Cheers, Ryan "wissam" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to check if current cell value is 1 and previous cell wa
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 "Mike H" wrote: 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. "wissam" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to check if current cell value is 1 and previous cell wa
Thank you very much.
It worked. "Rick Rothstein" wrote: 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" wrote in message ... 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. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to check if current cell value is 1 and previous cell wa
Thank you.
It worked. "Ryan H" wrote: 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 -- Cheers, Ryan "wissam" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare value in current cell to previous cell | Excel Worksheet Functions | |||
Need macro to check if cell is not blank & previous cell is blank, copy information from row above & paste | Excel Programming | |||
Check box, then add current time to cell | Excel Worksheet Functions | |||
formula, move to previous cell when the current cell=0 or empty | Excel Discussion (Misc queries) | |||
How do i check if the current cell is part of a range ? | Excel Programming |