Clearing the contents of a cell based on updates values of other cells

**123wc** · 01-24-2012, 12:55 PM

Hello,

Is it possible to clear the contents of say cell A4 (which is a drop down list that is displayed based on the contents of cells A1:A3) based on the condition if the contents of cells A1:A3 have been changed/updated?

The problem is that I don't want the user to be able to select some criteria for cell A4, then go back and change/update the dependent-upon contents of cells A1:A3 then forgetting to update the contents of A4.

Can this be done without a macro?

Thanks

**Palmetto** · 01-24-2012, 10:05 PM

No. It requires VBA code. Data validation lists will update if changes are made to the source range, but prior selections will not update or clear.

You could use conditional formatting to set the cell fill color when A1:A3 change by using a formula like so:

=isnumber(match(A4:A1:a3,0))

This would highlight the cell as a visual flag.
Alternately, if you have an adjacent cell avaiable, such as B4, you could test A4 against A1:A3 and display a brief message"

=if(isnumber(match(A4:A1:A3))=false,"update A1:A3 cells","")

**123wc** · 01-25-2012, 09:50 AM

Thank you very much for the helpful feedback. Could you please post a VBA code of how to do that? Thanks

**123wc** · 01-25-2012, 03:14 PM

Can anybody help please?

**Palmetto** · 01-25-2012, 09:11 PM

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("A1:A3")) Is Nothing Then
        Range("A4").ClearContents
    End If

End Sub

The code must go into the worksheet code module.
Right-click the tab of the target worksheet, choose View Code then copy and paste the above code into the code pane.
Close the VB Editor.

Anytime a change is made to cell A1:A3, cell A4 will be cleared.