do until loop with if statement that applies on different columns (vba)

[ddesantis]

Hi,
I'm scratching my head around what I think "a very simple piece" of code...but I'm a VBA beginner
I have to perform 2 tasks, and while I've completed the first one, the other is giving me a lot of troubles...

1st step: clear the content of a range of cells when I switch the xls validation list (yes,no). This works

Private Sub WorkSheet_Change(ByVal Target As Range)

If Target.Address = "$E$170" Then Range("$E$178:$E$187").ClearContents

If Target.Address = "$E$174" Then Range("$E$178:$E$187").ClearContents

2nd step:
- I have 2 ranges of value $D$178:$D$187 and $E$178:$E$187
- the range $D$178:$D$187 can assume the following values: Yes, No, Please select
- If any value in the range $D$178:$D$187 assume value "Yes" the corresponding value in the range $E$178:$E$187 has to assume value "Yes"
- If any value in the range $D$178:$D$187 assume value "No" the corresponding value in the range $E$178:$E$187 has to assume value "No"
- If any value in the range $D$178:$D$187 assume value "Please select" the corresponding value in the range $E$178:$E$187 will result in a validation list "Yes" or "No"

It's worth saying that the range $E$178:$E$187 has already a xls data validation list set (Yes,No)

Example
D180="Yes" then E180="Yes"

D183="No" then E183="No"

D185="Please select" then E185= validation list "Yes" or "No"

I tried as follows but xls crashed

Set Rng = Range("$E$178:$E$187")
Range("$D$178").Select
Do Until Selection.Value = "$D$188"
If Selection.Value = "Yes" Then
Rng = "Yes"
Else
Rng = "No"
End If
Loop

End Sub

my assumption is that in all other cases in the range $S$178:$E$187 (i.e. "Please select") the code doesn't apply and I can select "Yes" or "No" in the xls data validation list $E$178:$E$187

Hope it's clear, I would appreciate any help you can provide!

[xladept]

Maybe:

Please Login or Register  to view this content.

[mehmetcik]

try this

Please Login or Register  to view this content.

[ddesantis]

thanks mehmetcik, it works!!