Data Validation Question

[Guest]

I have set up Data Validation in cell Q10 that only allows text entered into
it if cell M10 is equal to "Offer Declined".

I have noticed though if you put the status of M10 to "Offer Declined", then
go to Q10, enter text, then change the status of M10 to something else, it
still allows the text.

I was wondering if there was something that I could do that would remove the
text in Q10 if M10 changes to anything other than "Offer Declined".

Any suggestions?

 

[Bernie Deitrick]

Alex,

You could use the worksheet change event: copy the code below, right-click the sheet tab, select
"View Code" and paste the code into the window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$M$10")) Is Nothing Then Exit Sub
If Range("$M$10").Value <> "Offer Declined" Then
Application.EnableEvents = False
Range("Q10").ClearContents
Application.EnableEvents = True
End If
End Sub

 

[Guest]

Cheers for that, it does work. One question though, if I want to have it
eligable for all cells from M10:M209 and Q10:Q209 how is this done, as I have
tried and it bought up an error message on the third line of your formula.

 

[Bernie Deitrick]

Alex,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range

If Intersect(Target, Range("$M$10:$M$209")) Is Nothing Then Exit Sub
For Each myCell In Intersect(Target, Range("$M$10:$M$209"))
If myCell.Value <> "Offer Declined" Then
Application.EnableEvents = False
myCell.Offset(0,4).ClearContents
Application.EnableEvents = True
End If
Next myCell
End Sub



HTH,
Bernie
MS Excel MVP

 

[Guest]

Excellent cheers for that.

To be even more annoying if for example I wanted only some text within a
cell, ie. anything in Column C10:C209 which contains the text "full time",
and using the same principal as below wanted it to remove anything in the
cells O10:O209 if full time did not appear in Column C, how would i got about
placing that in the formula?

 

[Bernie Deitrick]

Alex,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range

If Intersect(Target, Range("$M$10:$M$209")) Is Nothing Then Goto TryB
For Each myCell In Intersect(Target, Range("$M$10:$M$209"))
If myCell.Value <> "Offer Declined" Then
Application.EnableEvents = False
myCell.Offset(0,4).ClearContents
Application.EnableEvents = True
End If
Next myCell

TryB:

If Intersect(Target, Range("$C$10:$C$209")) Is Nothing Then Exit Sub
For Each myCell In Intersect(Target, Range("$C$10:$C$209"))
If myCell.Value <> "Full time" Then
Application.EnableEvents = False
myCell.Offset(0,12).ClearContents
Application.EnableEvents = True
End If
Next myCell

End Sub