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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,220
Messages
5,836,067
Members
430,404
Latest member
goncaloColt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top