Deleting data in cells based on data in another cell

[Detectiveclem]

I am using a dropdown list from G16:G2006. Within this list is the option of ‘Cancelled’if this is selected I want anything entered in the corresponding cells in columns ‘N’ & ‘O’ to be automatically deleted and the cells then left blank.

I considered having the formula placed in cells N16:N2006 & O16:O2006 but realised that if anyone entered data in the N or O cells this would overwrite the formula and therefore when the ‘Cancelled’ option in column G was selected the data in the corresponding cells in N & O wouldn’t be deleted.

Am I trying to do the impossible, or can someone help me resolve this problem.

Thank you

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Select "Cancelled" in any cell in your range.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G16:G2006")) Is Nothing Then Exit Sub
    If Target = "Cancelled" Then
        Range("N" & Target.Row).Resize(, 2).ClearContents
    End If
End Sub

 

[Detectiveclem]

Thank you Mumps, this works perfectly. If I also wished cells in other columns to also clear, how should I need to amend the formula, for example if I also wanted the corresponding contents in column D and H also cleared?

Really appreciate your help me.

Thanks again Paul

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G16:G2006")) Is Nothing Then Exit Sub
    If Target = "Cancelled" Then
        Intersect(Rows(Target.Row), Range("D:D,H:H,N:N,O:O")).ClearContents
    End If
End Sub

 

[Detectiveclem]

Thanks again Mumps, the original works well on most of my sheets, however when I try it on sheets which have already been populated and therefore some 'Cancelled' entries have been made on cells between G16:G2006 the macro does seem to run. Can you suggest anything to resolve this issue?

Thanks again

 

[mumps]

The macro I suggested is a Worksheet_Change event which means that it is triggered automatically when a change is made in the worksheet. In your case the change was limited to "G16:G2006". This will work with only one row at a time, the row in which the change is made. If you want to clear cells in multiple rows, you will need to run a macro manually. Place the macro below in a standard module and run it manually from there. The macro assumes you have headers in row 15 above your data. The Worksheet_Change macro will still work as before as you select "Cancelled" in your range.

Sub clearCells()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Cells(15, 1).CurrentRegion
        .AutoFilter 7, "Cancelled"
        Intersect(Rows("15:" & LastRow), Range("D:D,H:H,N:N,O:O")).SpecialCells(xlCellTypeVisible).ClearContents
        ActiveSheet.AutoFilterMode = False
    End With
    Application.ScreenUpdating = True
End Sub

 

[Detectiveclem]

Thank you Mumps. Just out of interest will this function even although it is a locked sheet with the user only have access to the unlocked cells, such as changing the category to Cancelled from a drop-down list?

Thanks again,

 

[mumps]

If the sheet is protected, try these macros. Change all occurrences of the password (in red) to suit your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G16:G2006")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="[COLOR="#B22222"]MyPassword[/COLOR]"
    If Target = "Cancelled" Then
        Intersect(Rows(Target.Row), Range("D:D,H:H,N:N,O:O")).ClearContents
    End If
    ActiveSheet.Protect Password:="[COLOR="#B22222"]MyPassword[/COLOR]"
End Sub

Sub clearCells()
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="[COLOR="#B22222"]MyPassword[/COLOR]"
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Cells(15, 1).CurrentRegion
        .AutoFilter 7, "Cancelled"
        Intersect(Rows("15:" & LastRow), Range("D:D,H:H,N:N,O:O")).SpecialCells(xlCellTypeVisible).ClearContents
        ActiveSheet.AutoFilterMode = False
    End With
    ActiveSheet.Protect Password:="[COLOR="#B22222"]MyPassword[/COLOR]"
    Application.ScreenUpdating = True
End Sub

 

[Detectiveclem]

Thank you mumps, you are a genius. I really appreciate all your help.

 

[mumps]

My pleasure.