VBA Help with Data Validation

[Canadianexcel]

Good Afternoon,

I am a bit stuck whatever I try is not working out as well as I hoped. Here is what I am trying to do.

- I have a Data Validation List in Column C a Yes No N/A and another list in Column D with a scale from 1-5.
- What I need is to clear any value in Column D if my first list is set changed to No or N/A.

I have attempted several example I found on google in the Private Sub Worksheet_Change(ByVal Target As Range). I was close with the following code my no cigar and I think I am looking at it to long, any help would be awesome

Dim ws As Worksheet
Dim x As Integer
Dim clearRng As Range


Application.ScreenUpdating = False


Set ws = ThisWorkbook.Sheets("Sheet1")


For x = 1 To ws.Range("J" & Rows.Count).End(xlUp).Row
If ws.Range("j" & x).Value = "No" Then
ws.Range("k" & x).Clear
ws.Range("k" & x).Select
ws.Range("k" & x).Value = 0
With Selection.Interior
.Pattern = xlUp
.PatternColor = 5287936
.ColorIndex = xlAutomatic
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ElseIf ws.Range("j" & x).Value <> "No" Then
ws.Range("k" & x).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If



Next x


Application.ScreenUpdating = True


End Sub

 

[Fluff]

Is this what you want?

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("C:C")) Is Nothing Then
      If Target.Value <> "Yes" Then Target.Offset(, 1).Value = ""
   End If
End Sub

 

[Canadianexcel]

Thank so far first test work

 

[Fluff]

You're welcome, let me know how it goes.

 

[Canadianexcel]

You're welcome, let me know how it goes.

Yes Thank you this is what I was looking for.

 

[Fluff]

Glad it helped & thanks for the feedback