Error when I delete content in multiple cells simultaneously

[Navina Gawade]

As I'm new to VBA I have tried following code which is working perfectly unless and until I delete some content in multiple cells of column B simultaneously.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
If Target.Value = "Shares" Then
Target.Offset(0, 1).Value = "Equity"
End If
End If
End Sub
(I used this code so that whenever I'll enter Shares in column B automatically equity will be popup in column C and whenever I'll type something other than shares column C will remain as it is. )

Can someone help me with code which will not result in such error as I'm totally unaware of VBA working.

 

[Joe4]

Welcome to the Board!

See if this fixes your issue:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
   
    If (Target.Column = 2) And (Target.Value = "Shares") Then
        Target.Offset(0, 1).Value = "Equity"
    End If

End Sub

 

[Navina Gawade]

Welcome to the Board!

See if this fixes your issue:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.CountLarge > 1 Then Exit Sub
  
    If (Target.Column = 2) And (Target.Value = "Shares") Then
        Target.Offset(0, 1).Value = "Equity"
    End If

End Sub

Yes it's working now.
Thank you so much for this.

 

[Navina Gawade]

Yes it's working now.
Thank you so much for this.

But now it's giving me same error whenever I delete content in other than column B.

 

[mohadin]

Hi
Try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target.Columns, Columns(2)) Is Nothing Then
        If Target.Value = "Shares" Then
            Target.Offset(0, 1).Value = "Equity"
        End If
    End If
End Sub

 

[Joe4]

But now it's giving me same error whenever I delete content in other than column B.

Can you give me an example of exactly what you are doing?
Are you deleting whole rows or columns?
If so, which ones?

 

[Navina Gawade]

Can you give me an example of exactly what you are doing?
Are you deleting whole rows or columns?
If so, which ones?

I was actually using "2" instead of "1" in IF target.countlarge>1 as I shifted date from b column to C column and C column to D column. But now I used "1" and it's not giving me error now. Thank you so much for help.