VBA worksheet change with multiple cells

Question

I have a named range that I'm monitoring in a Worksheet_Change(ByVal Target as Range). I noticed that if the user selects multiple cells in that range and does a right-click-->Clear, the program crashes.

I have code that looks like :

If Target <> "" And (.Cells(Target.Row, [GLB_Location_Col].Column) = "" _
    Or .Cells(Target.Row, [GLB_LineType_Col].Column) = "") Then                   
    .Cells(Target.Row, [GLB_Location_Col].Column).Interior.ColorIndex = 40  'peach
    .Cells(Target.Row, [GLB_LineType_Col].Column).Interior.ColorIndex = 40  'peach

My code is assuming 1 cell is being changed at a time, so I guess when a range gets passed to one of my .Cell functions, it doesn't know how to handle it and throws a mismatch error. Is there a way to prevent this from happening?

Answer 1

First ensure that the code runs if only 1 cell is selected.

If Target.Cells.Count = 1 Then
    '~~> rest of code here
End If

Note: For newer versions of Excel (e.g. XL2007 and up) you'll need to use CountLarge property to avoid overflow error.

Now, to test for specific ranges only, then integrate Intersect function in your If statement. Something like:

If Not Intersect(Target, .Cells(1, [GLB_Location_Col].Column).EntireColumn) Is Nothing _
And .Cells(Target.Row, [GLB_Location_Col].Column) <> "" Then

So it checks first if Target range is within the entire GLB_Location_Col (which I assumed a named Range) and also if the range is not empty. HTH.