VBA code to have dependant cell on a different row to the input cell

[Ancien1]

Hi,
I have a worksheet where a validation list choice in say sell A1 changes the validation list choices in B1 and if the validation list choice in A1 is changed the content of cell B1 is cleared allowing a different list of choices to be used.

The VBA code for this is:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 2 Then
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
Target.Offset(0, 1).ClearContents
End If
End If

exitHandler:
Application.EnableEvents = True
Exit Sub
End Sub

However I want the dependant cell to be on a different row, say B2 instead of B1. Is this possible and of so how?

 

[Ancien1]

Likewise if COMP is turned off then THRESHOLD and OUTPUT completely disappear, if REVERB is turned off then LEVEL, SIZE and COLOUR disappear, if FX 1 is turned off LEVEL, FEEDBACK, DEPTH and RATE disappear, if FX 2 is turned off LEVEL, DELAY(L), DELAY(R), DAMP and FEEDBACK disappear.

 

[Ancien1]

So, in simple terms then, if entry is made in I8 and it is 'Off' then, you want I10, K10, M10, O10 to be cleared?
Similarly, with other sections eg Q15, entry of 'Off' to trigger clearance of Q17, T17, V17, X17, Z17 ?

Yes

 

[Snakehips]

Remove or disable any other Worksheet_Change code you may have for the sheet in question and then see if this does it.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("I8:AA8")) Is Nothing And Intersect(Target, Range("I15:AA15")) Is Nothing Then Exit Sub
If Target.Value = "On" Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Select Case Target.Address
    Case "$I$8"    '  EQ
        Range("I10:P11") = vbNullString
      
      Case "$Q$8"    ' COMP
         Range("Q10:U11") = vbNullString
        
      Case "$V$8"   '  REVERB
         Range("V10:AA11") = vbNullString
                  
      Case "$I$15"   '  FX 1
         Range("I17:P18") = vbNullString
         
        Case "$Q$15"  'FX 2
         Range("Q17:AA18") = vbNullString
               
        Case Else
End Select

exitHandler:
Application.EnableEvents = True
On Error GoTo 0
Exit Sub
End Sub

 

[Ancien1]

Remove or disable any other Worksheet_Change code you may have for the sheet in question and then see if this does it.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("I8:AA8")) Is Nothing And Intersect(Target, Range("I15:AA15")) Is Nothing Then Exit Sub
If Target.Value = "On" Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Select Case Target.Address
    Case "$I$8"    '  EQ
        Range("I10:P11") = vbNullString
     
      Case "$Q$8"    ' COMP
         Range("Q10:U11") = vbNullString
       
      Case "$V$8"   '  REVERB
         Range("V10:AA11") = vbNullString
                 
      Case "$I$15"   '  FX 1
         Range("I17:P18") = vbNullString
        
        Case "$Q$15"  'FX 2
         Range("Q17:AA18") = vbNullString
              
        Case Else
End Select

exitHandler:
Application.EnableEvents = True
On Error GoTo 0
Exit Sub
End Sub

Tony, thank you so much, that is great!. I owe you a few beers, Den

 

[Ancien1]

Tony, thank you so much, that is great!. I owe you a few beers, Den

I thought your Christian name is if it's not then just to confirm my thank you message was to Snakehips