The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.
Become a Premium Member and unlock a new, free course in leading technologies each month.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
'only one cell updated
If Target.Column = 2 Then
'update is column B , our monitor target
Target.offset(0, -1).value = Target.offset(0, 1).value
Target.value = 0
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("Z:Z")) Is Nothing Then
Select Case Range("AB" & Target.row).value
Case Is = ""
Range("M" & Target.row) = Range("M" & Target.row).value + 1
Case Is = "1"
Range("N" & Target.row) = ""
End Select
End If
End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Private Sub Worksheet_Change(ByVal Target As Range)
' Check Condition 1
If Target.Count = 1 Then
'only one cell updated
If Target.Column = 2 Then
'update is column B , our monitor target
Target.offset(0, -1).value = Target.offset(0, 1).value
Target.value = 0
End If
End If
' Check Condition 2
If Not Intersect(Target, Me.Range("Z:Z")) Is Nothing Then
Select Case Range("AB" & Target.row).value
Case Is = ""
Range("M" & Target.row) = Range("M" & Target.row).value + 1
Case Is = "1"
Range("N" & Target.row) = ""
End Select
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' Check Condition 1
If Target.Count = 1 Then
'only one cell updated
If Target.Column = 2 Then
'update is column B , our monitor target
Target.offset(0, -1).value = Target.offset(0, 1).value
Target.value = 0
End If
ElseIf Not Intersect(Target, Me.Range("Z:Z")) Is Nothing Then
' Check Condition 2
Select Case Range("AB" & Target.row).value
Case Is = ""
Range("M" & Target.row) = Range("M" & Target.row).value + 1
Case Is = "1"
Range("N" & Target.row) = ""
End Select
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
' Check Condition 1
If (Target.Count = 1) And (Target.Column = 2) Then
'only one cell updated in column B
'update is column B , our monitor target
Target.Offset(0, -1).Value = Target.Offset(0, 1).Value
Target.Value = 0
ElseIf Not Intersect(Target, Range("Z:Z")) Is Nothing Then
' Check Condition 2
Select Case Range("AB" & Target.Row).Value
Case Is = ""
Range("M" & Target.Row) = Range("M" & Target.Row).Value + 1
Case Is = 1 'You probably are entering the number 1 in the cell, not text
Range("N" & Target.Row) = ""
End Select
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If (Target.Count = 1) And (Target.Column = 2) Then ' Check Condition 1: only one cell is updated in column B
Target.Offset(0, -1).Value = Target.Offset(0, 1).Value
Target.Value = 0
ElseIf Not Intersect(Target, Range("Z:Z")) Is Nothing Then ' Check Condition 2: one or more cells are updated in column Z, and column AA is either blank or 1
Select Case Range("AA" & Target.Row).Value
Case Is = ""
Range("AB" & Target.Row) = Range("AB" & Target.Row).Value + 1
Case Is = 1 'You probably are entering the number 1 in the cell, not text
Range("AB" & Target.Row) = "" 'Set column AB equal to an empty string (looks like a blank)
'Range("AB" & Target.Row).ClearContents 'Clear contents of column AB
End Select
End If
Application.EnableEvents = True
End Sub
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.