VBA change event applied to one or multiple cells

[FryGirl]

How can I change this to apply to multiple cells at once? Right now this will change "A" when I enter something in "B", but what if I select multiple contiguous cells like B2:B10 and then I want A2:A10 to equal "IN".

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Value <> "" Then Range("A" & Target.Row).Value = "IN"
    Application.EnableEvents = True
End Sub

 

[Colin Legg]

If you change B2:B10 at once (eg with CTRL+ENTER) then Target will hold a reference to that entire range so you could loop through Target.Cells to examine each individual cell. By the way, your code changes column A when any cell across the row (including A) is changed, not just column B, so perhaps you could also modify it to only loop through the Target cells in column B. An example might be something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngCell As Range
    Dim rngCheck As Range
    
    'only check cells in column B
    Set rngCheck = Intersect(Target, Columns(2))
    
    If Not rngCheck Is Nothing Then
    
        Application.EnableEvents = False
    
        For Each rngCell In rngCheck.Cells
            
            If Not IsEmpty(rngCell.Value2) Then
                
                'put "IN" in column A
                Cells(rngCell.Row, 1).Value = "IN"
            End If
    
        Next rngCell
    
        Application.EnableEvents = True
    End If
    
End Sub

 

[Jeffrey Mahoney]

How about this?

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cel As Range
  Dim Ar As Range
  
  Application.EnableEvents = False
  For Each Ar In Target.Areas               'In case multiple areas have been changed (filters on)
    For Each Cel In Ar                      'Cells in each Area
      If Cel.Value <> "" And Cel.Column = 2 Then Range("A" & Cel.Row).Value = "IN"
    Next Cel
  Next Ar
  
  Application.EnableEvents = True
End Sub

 

[FryGirl]

These are both perfect solutions. Thank you for your time.