VBA beginner here.
I have project where I have specified input cells for the user. When one of these input cells is changed, it needs to run a few lines of code that are specific to only that one cell. If the user clears the contents of the cell, I want the code to replace the blank cell with the value "0".
The code below simulates what I am trying to achieve. It is written in the same form as my project but is more succinct.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row = 1 Then
Range("B1").Value = "Changed 1" 'Just something specific to this cell. Not important
If IsEmpty(Sheet1.Range("A1")) Then Sheet1.Range("A1").Value = 0
End If
If Target.Column = 1 And Target.Row = 2 Then
Range("B2").Value = "Changed 2" 'Just something specific to this cell. Not important
If IsEmpty(Sheet1.Range("A2")) Then Sheet1.Range("A2").Value = 0
End If
If Target.Column = 1 And Target.Row = 3 Then
Range("B3").Value = "Changed 3" 'Just something specific to this cell. Not important
If IsEmpty(Sheet1.Range("A3")) Then Sheet1.Range("A3").Value = 0
End If
End Sub
Everything above works fine when the changes are performed on single cells. If the user selects all the cells and presses the delete key, it only runs the code for the first cell. I want it to run for all the selected (deleted) cells.
Any advice on how to simultaneously run the Worksheet_Change across multiple cells?
Thanks
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Cells(1, 1)) Is Nothing Then
Range("B1").Value = "Changed 1" 'Just something specific to this cell. Not important
If IsEmpty(Sheet1.Range("A1")) Then Sheet1.Range("A1").Value = 0
End If
If Not Application.Intersect(Target, Cells(1, 2)) Is Nothing Then
Range("B2").Value = "Changed 2" 'Just something specific to this cell. Not important
If IsEmpty(Sheet1.Range("A2")) Then Sheet1.Range("A2").Value = 0
End If
If Not Application.Intersect(Target, Cells(1, 3)) Then
Range("B3").Value = "Changed 3" 'Just something specific to this cell. Not important
If IsEmpty(Sheet1.Range("A3")) Then Sheet1.Range("A3").Value = 0
End If
End Sub
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments