Dynamic Range for clear cells on change

[Snickers65]

I am working on a "SUPER RUSH--GET IT DONE LAST WEEK" project that requires a dynamic range of rows. There are dependent data validation drop down lists involved. I want to be able to clear cells when one of the drop down values changes.

For example: User originally chose "Apples" from drop down list in Column E, then "Granny Smith" from drop down list in Column F, but wants to make it "Oranges", and "Valencia", or perhaps just clear that row and not make any more choices at this time.

Based on their choice of Granny Smith Apples, a price populates automatically in Column G using INDEX MATCH MATCH formula. When the user changes their mind, I want to clear Column E and F, thereby clearing Column G automatically (but NOT clear the formula!). The user could clear Column E by deleting contents in Column E, or just making another choice.

I have found code online that works for a static range, but only if user makes an alternate choice. I need to make it a dynamic range (not just "E22:E32"), AND/OR clear contents if user deletes previous contents.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sValue As String
    
    If Intersect(Target, Range("E22:E32")) Is Nothing Or _
        Target.Count > 1 Or _
        Target.Text = vbNullString Then Exit Sub
        
    On Error GoTo CleanUp
    Application.EnableEvents = False
    
    sValue = Target.Text
    Intersect(Target.EntireRow, Columns("F:F")).ClearContents
    Target = sValue
   
CleanUp:
    Application.EnableEvents = True
End Sub

I have not worked with vba for many years, so I am really having problems with something that should be relatively simple. Any help would be VERY APPRECIATED!!!!

[ncmay]

This may help

The macro below will clear the cell contents below row 1.
Columns B and C of the target row will be cleared if column A changes.
Column C of the target row will be cleared if column B changes.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = 1 Then Exit Sub
  If Target.Column > 2 Then Exit Sub
  On Error GoTo ErrHandler
  Application.EnableEvents = False
  Select Case Target.Column
    Case 1
       Target.Offset(0, 1).Value = ""
       Target.Offset(0, 2).Value = ""
    Case 2
       Target.Offset(0, 1).Value = ""
  End Select
ErrHandler:
  Application.EnableEvents = True
End Sub

[Snickers65]

I appreciate your response, however, it doesn't seem to work. I replaced my original code with what you provided, and made the following changes:

If Target.Row = 22 Then Exit Sub  'all changes will be row 23 or below.
  If Target.Column > 6 Then Exit Sub  'user initiated changes will be in columns E:F.

This results in no changes at all, except the ones I initiate. Cells don't clear at all. Am I missing something? Am I being completely dense? (Wouldn't surprise me a bit right now -brain drain to the max...LOL)

[ncmay]

Try the following code

Private Sub Worksheet_Change(ByVal Target As Range)
  
  If Target.Row < 23 Then Exit Sub  'all changes will be row 23 or below.
  If Target.Column = 5 Then ' If changes are made in column E the adjacent cell in column F will be cleared
  
  On Error GoTo ErrHandler
  Application.EnableEvents = False
       Target.Offset(0, 1).Value = ""
  End If
ErrHandler:
  Application.EnableEvents = True
End Sub

[Snickers65]

That seems to work perfectly! Thank you!!
Now, my boss is considering a change to the format of the project. If he goes with what he is thinking, I will need to have this stop at a certain place, but the row number will still be variable. How is the best way to do that? For example: same as above, only now he wants to add a Grand Total row, and have a totally different set of info below that. I will need the text clearing code to stop at the row above the Grand Total row so we don't have problems with the cells below. Sorry to change it up on you, but the boss is the boss!

[ncmay]

Uploading a sample workbook would be helpful