Detect cell value changes

[nanefy]

Hi,

I wonder if someone knows how to do this, as I have scoured the forum and cant really find what I am looking for.

I have two columns A is Category and B is Sub Category i.e.

Input Missing Documents
DMC Archive

Column B uses validation of the form indirect(A1), so that if A1 = input then it will bring up a certain drop down list. Now if they choose Input for A1 and Missing Documents for B1 then change thier mind and Choose DMC for A1 but leave Missing Documents in B1 then B1 does not contain the correct info (this is the users fault). I want to prevent this. Is there a way to check and see if A1 has changed and if so clear the contents of B1 so that they have to re-select.

 

[cfree36]

You could put this code the worksheet module where you list is.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$B$5" Then
    Range("C5").ClearContents
End If
End Sub

Assuming your first drop down is in B5 and you second is in C5
then you could use conditional formatting to make the cell red if it is blank.

 

[nanefy]

Hi,

Thats great, however I dont want to hardcode the cells into the code. Although my example used A1 & B1 I want it to effect all the cells in A1 & B1. How is this done?

 

[cfree36]

This will clear the contents of the cell directly to whatever cell is selected in column A

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
    Set c = ActiveCell
    c.Offset(0, 1).ClearContents
    End If
End Sub