VBA Code to Clear Contents of Cell based on Another Cell

[leonalap24]

Hi, I need help writing a code that will clear the contents of cells D10:G10 & I10:M10 if the contents of cell C10 are deleted. I need for the corresponding rows down do D33:G33 & I33:M33 & C33.

I am able to make it work for the first row but not sure how to include the additional rows without referencing each row individually.

Please help.

Thanks in advance.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Clear C10.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C10")) Is Nothing Then Exit Sub
    If Target = "" Then
        Range("D10:G33, I10:M33, C33").ClearContents
    End If
End Sub

 

[Osvaldo Palmeiro]

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address <> "$C$10" And Target.Address <> "$C$33" Then Exit Sub
 If Target.Value <> "" Then Exit Sub
  Union(Range(Cells(Target.Row, 4), Cells(Target.Row, 7)), Range(Cells(Target.Row, 9), Cells(Target.Row, 13))).ClearContents
End Sub

 

[leonalap24]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Clear C10.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C10")) Is Nothing Then Exit Sub
    If Target = "" Then
        Range("D10:G33, I10:M33, C33").ClearContents
    End If
End Sub

I think I might not have stated what I needed exactly. I need column C10 to clear D10:G10 & I10:M10 and I need C11 to clear D11:G11 & I11:M11 and so forth all the way down to row 33. This worked but it was all dependent on C10 to clear everything else. Is there a way to adjust it so it will work?? Thanks so much for your help!

 

[leonalap24]

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address <> "$C$10" And Target.Address <> "$C$33" Then Exit Sub
 If Target.Value <> "" Then Exit Sub
  Union(Range(Cells(Target.Row, 4), Cells(Target.Row, 7)), Range(Cells(Target.Row, 9), Cells(Target.Row, 13))).ClearContents
End Sub

I also tried this one and it worked for Rows 10 & 33 but nothing in between. Please let me know if there is another way to do this. Thanks for your help.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C10:C33")) Is Nothing Then Exit Sub
    If Target = "" Then
        Range("D" & Target.Row & ":G" & Target.Row).ClearContents
        Range("I" & Target.Row & ":M" & Target.Row).ClearContents
    End If
End Sub

 

[leonalap24]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C10:C33")) Is Nothing Then Exit Sub
    If Target = "" Then
        Range("D" & Target.Row & ":G" & Target.Row).ClearContents
        Range("I" & Target.Row & ":M" & Target.Row).ClearContents
    End If
End Sub

This worked great for all the rows except for I get a runtime error 13 type mismatch. Is there any way around that? Thanks again!

 

[mumps]

I don't know why you're getting that error. I tried it on a dummy sheet and it worked for me. I think that it would be much easier to follow if I could see how your data is organized. Perhaps you could upload a copy of your file to a free site such as www.box.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets.

 

[leonalap24]

https://app.box.com/s/zdbgzaoio89yzb9re0yt

Here is a link to the workbook. Sheet 1 is what I am currently working on. The error happens when C10 is selected and used to auto fill C11:C33. Please let me know your thoughts . Thanks so much for all of your help.

 

[Osvaldo Palmeiro]

Try adding the blue line.

Private Sub Worksheet_Change(ByVal Target As Range)
  [COLOR=#0000ff]If target.Count > 1 Then Exit Sub[/COLOR]
...
...
...