Validation & Worksheet_Change & Delete Multiple Selection Problem

Ramses505

New Member
Joined
Oct 17, 2016
Messages
34
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:
<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.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,746
Office Version
  1. 365
Platform
  1. Windows
Try this

Code:
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

New Member
Joined
Oct 17, 2016
Messages
34
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,026
Messages
5,526,325
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top