none
Macro to clear cell content if cell on the same row is modified based on a drop down list RRS feed

  • General discussion

  • Hi,


    I created a worksheet being filled in manually by users everyday. It has 5 columns. Column A is manually keyed in (date). Column B3:B200 is populated based on a dropdown list. Column C is formula which we can ignore in this discussion. Column D3:D:200 is populated based on a dropdown list INDIRECT to column B and likewise Column
    F3:F200 is populated based on a dropdown list INDIRECT to column D.

    My problem now is, assuming the 5 columns is populated, If the user modifies any cell in Column B, data in columns D and F will remain and most of the time, it is not related to the INDIRECT list in column B and C, respectively. It is always neglected to be updated. I can delete the full data in B3:F200 but it will be inefficient because the data may be valid for the next day.

    Can anyone help me with a macro so that, if any cells in column B3:B200 is modified, the respective column D and F on the same row should be cleared.

    Example, B3 = ABC, D3=1 and F3=2. if B3 is modified, say the user pull down the list and select CBA, the contents of D3 and E3 should be cleared and let the user populate with the new data.

    Please help.

    Thanks.
    Saturday, August 16, 2014 4:16 AM

All replies

  • Right-click the sheet tab of the worksheet.

    Select 'View Code' from the context menu.

    Add the following code to the worksheet module that appears:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cel As Range
        ' Clear D and F if B is changed
        If Not Intersect(Range("B:B"), Target) Is Nothing Then
            Application.EnableEvents = False
            For Each cel In Intersect(Range("B:B"), Target)
                cel.Offset(0, 2).ClearContents
                cel.Offset(0, 4).ClearContents
            Next cel
            Application.EnableEvents = True
        End If
        ' Optional: clear F if D is changed
        If Not Intersect(Range("D:D"), Target) Is Nothing Then
            Application.EnableEvents = False
            For Each cel In Intersect(Range("D:D"), Target)
                cel.Offset(0, 2).ClearContents
            Next cel
            Application.EnableEvents = True
        End If
    End Sub

    Warning: an inevitable side effect of this code is that Undo won't be available when you edit a cell in column B (and in column D, if you keep the optional part of the code).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, August 16, 2014 10:52 AM
  • wow! that's owe some! Thanks so much.
    Saturday, August 16, 2014 11:06 AM