Dim inuse As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c, r As Integer
If Not inuse Then
inuse = True
c = Target.Cells.Column
r = Target.Cells.Row
'column 1 is A
If c = 1 Then
'The change came from A1-A3
If r > 0 And r < 4 Then
Me.Cells(4, 1) = 0
End If
'the change came from A4
If r = 4 Then
Me.Range("A1", "A3") = 0
End If
End If
DoEvents
inuse = False
End If
End Sub
Dim inuse As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c, r As Integer
If Not inuse Then
inuse = True
c = Target.Cells.Column
r = Target.Cells.Row
'column 1, 5, 10 handle the change
If c = 1 Or c = 5 Or c = 10 Then
HandleChange r, c
End If
DoEvents
inuse = False
End If
End Sub
Private Sub HandleChange(r As Integer, c As Integer)
'The change came from Row 1-3
Dim st As String
If r > 0 And r < 4 Then
Me.Cells(4, c) = 0
End If
'the change came from row 4
If r = 4 Then
st = Chr(64 + c)
Me.Range(st & "1", st & "3") = 0
End If
End Sub
If c = 1 Or c = 5 Or c = 10 Then
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Integer
Dim r As Integer
If Not inuse Then
inuse = True
c = Target.Cells.Column
r = Target.Cells.Row
'column 1, 5, 10 handle the change
If c = 1 Or c = 5 Or c = 10 Then
HandleChange r, c
End If
DoEvents
inuse = False
End If
End Sub
Private Sub HandleChange(r As Integer, c As Integer)
'The change came from Row 1-3
Dim st As String
If r > 0 And r < 4 Then
Me.Cells(4, c) = 0
End If
'the change came from row 4
If r = 4 Then
st = Chr(64 + c)
Me.Range(st & "1", st & "3") = 0
End If
End Sub
Replace the code with this
Title | # Comments | Views | Activity |
---|---|---|---|
How to extract and populate data based on 2 matching cells | 3 | 28 | |
Opening second Excel workbook and copying contents of worksheet back to first workbook | 8 | 20 | |
MS Excel "--" in Formula What is it doing? | 3 | 9 | |
Excel | 3 | 20 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
12 Experts available now in Live!