clear contents of adjacent cell(s) on change

[blackie42]

Hi,

I have a drop down list in column G, a dependent list in column H (using INDIRECT) and
another dependent list in column I (using SUBSTITUTE). 1000 Rows.

I'd like to clear columns H & I when column G is changed and also clear column I when only
column H is changed.

Any help appreciated

thanks
Jon

[SamT]

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.Enable Events = False
    If Not Intersect(Target, Range("G:G")) Is Nothing Then
      Range("H:I").ClearContents
      GoTo AllDone
    End If

    If Not Intersect(Target, Range("H:H")) Is Nothing Then Range("I:I").ClearContents

AllDone:
  Application.EnableEvents = True
End Sub

[blackie42]

Apologies - might not have been specific enough.

The code bugs out if I leave the 'Application.EnableEvents' in andif I take out although it does work it clears everything i.e. the whole column(s)

What I wanted was something similar but for separate rows e.g. change G2 - H2 & I2 clear and not the whole columns, and if I change G14, only H2 & I2 would be affected.

thanks
Jon

[SamT]

The code bugs out

I don't understand.

it does work it clears everything i.e. the whole column(s)

As planned.

change G2 - H2 & I2 clear and not the whole columns

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.Enable Events = False
    If Not Intersect(Target, Range("G:G")) Is Nothing Then _
      Range("H2:I2").ClearContents

  Application.EnableEvents = True
End Sub

[Aflatoon]

Perhaps you mean:

Private Sub Worksheet_Change(ByVal Target As Range)  
Application.Enable Events = False
If Not Intersect(Target, Range("G:G")) Is Nothing Then
Target.Offset(, 1).Resize(, 2).ClearContents
GoTo AllDone
End If
If Not Intersect(Target, Range("H:H")) Is Nothing Then Target.Offset(, 1).Resize(, 1).ClearContents
AllDone:
Application.EnableEvents = True
End Sub

[blackie42]

Thanks Guys - probably didn't explain too well.

Aflatoons extended code using resize does the job

regards
Jon

[SamT]

Yeah, you really have to work on explaining.

clear columns H & I when column G is changed and also clear column I

change G2 - H2 & I2 clear and not the whole columns, and if I change G14, only H2 & I2 would be affected.

[Aussiebear]

Are you missing an End If for the second If not intersect?

[SamT]

@ Ted,

No, it's a one line If.