Worksheet_Change Macro - Changing multiple cells

Question

I wrote a macro and while it works, functionally its not what is needed. It's an interactive checklist that breaks down multiple areas of machines and if they are working checks them off and then this updates a master list with multiple sections. However, it only works with one cell at a time and it needs to be able to work with multiple cells at a time (both with rows and columns). Here's my current code:

'Updates needed:
'       Make so more than one cell works at a time
'       in both x and y directions

Private Sub Worksheet_Change(ByVal Target As Excel.range)
    Dim wb As Workbook
    Dim mWS As Worksheet
    Dim conName As String
    Dim mCol As range
    Dim mCon As Integer
    Dim count As Long
    Dim cell As range
    Dim y As String

    count = 1
    y = ""
    Set wb = ActiveWorkbook
    Set mWS = wb.Sheets("Master")
    Set mCol = mWS.range("B:B")
    mCon = 0


   'Selects the name of the string value in which we need to search for in master list
    If Target.Column < 100 Then
       ThisRow = Target.Row
       conName = ActiveSheet.Cells(ThisRow, "B")
       y = Target.Value
    End If 

    'search for matching string value in master list
    For Each cell In mCol
        If cell.Value = conName Then
            mCon = count
                Exit For
        End If
       count = count + 1
    Next
  'mark as "x" in Master list
   Dim cVal As Variant
   Set cVal = mWS.Cells(count, Target.Column)
   cVal.Value = y
End Sub

What is happening - If I drag down "x" for multiple rows or columns my codes breaks at y = Target.Value and will only update the cell I first selected and its counterpart on the master list. What it should do is if I drag and drop the "x" onto multiple rows of columns it should update all of them in the sheet I'm working on and the master list. I only set up the macro for one cell at a time and I have no idea how to set it up for dragging and dropping the "x" value for multiple rows

Answer 1

You need to iterate through the cells using a ForEach loop.

Also, you may be better using the Selection object rather than Target

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


For Each cell In Selection
    Debug.Print cell.Address
Next cell



Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Exit Sub

Answer 2

I think you need a For ... Each iteration over the Target in order to work with multiple cells. As Michael noted in the comments, the _Change event fires only once, but the Target reflects all cell(s) that changed, so you should be able to iterate over the Target range. I tested using this simple event handler:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Dim myCell As Range
Set myRange = Target

For Each myCell In myRange.Cells
    Debug.Print myCell.Address
Next

End Sub

I am not able to test obviously on your data/worksheet, but I think it should put you on the right track.

Private Sub Worksheet_Change(ByVal Target As Excel.range)
Dim wb As Workbook
Dim mWS As Worksheet
Dim conName As String
Dim mCol As range
Dim mCon As Integer
Dim count As Long
Dim cell As range
Dim y As String

count = 1
y = ""
Set wb = ActiveWorkbook
Set mWS = wb.Sheets("Master")
Set mCol = mWS.range("B:B")
mCon = 0

'Add some new variables:
Dim myRange as Range
Dim myCell as Range
Set myRange = Target

Application.EnableEvents = False '## prevents infinite loop
For each myCell in myRange.Cells
    If myCell.Column < 100 Then
       ThisRow = myCell.Row
       conName = ActiveSheet.Cells(ThisRow, "B")
       y = myCell.Value
    End If 

    'search for matching string value in master list
    For Each cell In mCol
        If cell.Value = conName Then
            mCon = count
                Exit For
        End If
       count = count + 1
    Next
  'mark as "x" in Master list
   Dim cVal As Variant
   Set cVal = mWS.Cells(count, Target.Column)
   cVal.Value = y

Next
Application.EnableEvents = True '## restores event handling to True
End Sub