Validation & Worksheet_Change & Delete Multiple Selection Problem

[Ramses505]

I have a range of cells with dropdowns to select names supplioed from a list - that works great.

On the sheet containing those I have some Worksheet_Change code like this;

<code>Private Sub Worksheet_Change(ByVal Target As Range)
  If Range("A1").Value = "" Then                                           ' Can be FLAGGED to skip for Developement
    If Not Intersect(Target, Range("TestNameBlock")) Is Nothing Or _
       Not Intersect(Target, Range("TestNameBlock2")) Is Nothing Then      ' Color selected Name with Staff Color
      If Target.Value <> "" Then                                           '   Staff Name Picked
        Target.Interior.ColorIndex = LookUp_Staff_Colour(ActiveCell.Value) '     Look up Staff Color Value using Function
      Else                                                                 '   Name Cleared
        Target.Interior.ColorIndex = xlNone                                '     NO Color
      End If                                                               '
    End If                                                                 '
  End If                                                                   '
End Sub                                                                    '</code>

This code colours the cell with a colour from a lookup on another sheet (the colour associated with the selected staff name from the list. This works fine as well.

The problem comes if you select a group of these validated cells and then delete the contents using the Delete Key - the the code faults as I suppose it can't do the lookup.

1) Am I doing the coloring in the 'right' way ?
2) What can I do about the Delete Key problem ?
3) Any other ideas ?


Thanks for taking the time to read.

 

[Yongle]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cel As Range
  If Range("A1").Value = "" Then                                                ' Can be FLAGGED to skip for Developement
    If Not Intersect(Target, Range("TestNameBlock")) Is Nothing Or _
            Not Intersect(Target, Range("TestNameBlock2")) Is Nothing Then      ' Color selected Name with Staff Color
        For Each Cel In Target
            If Cel <> "" Then                                                   ' Staff Name Picked
                Cel.Interior.ColorIndex = LookUp_Staff_Colour(ActiveCell.Value) ' Look up Staff Color Value using Function
            Else                                                                '   Name Cleared
                Cel.Interior.ColorIndex = xlNone                                ' NO Color
            End If
        Next
    End If
  End If
End Sub

 

[Ramses505]

Thanks for that Yongle. I have decided to test for multiple selections and then branch the code. Appreciate you taking the time to answer, I always learn something.

Thanks