Repeat VBA code for each cell

[j.stitt1]

Hello,

I have this code that works for the cell intended but i would like it available for a range of cell C4 thru C103.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C4")) Is Nothing Then
If Range("C4") = "" Then Range("D4:V4").ClearContents
If Range("C4") <> "" Then Range("D4:V4").ClearContents
End If
End Sub

Any help would be grateful

Thanks
Jeffrey

[Paul_Hossler]

1. You should use the[#] icon to insert CODE tags and then paste ypur macro between them

2. These don't make sense since D-V get cleared regardless of the C


If Range("C4") = "" Then Range("D4:V4").ClearContents
If Range("C4") <> "" Then Range("D4:V4").ClearContents



3. You should disable event handling when you're changing the WS from inside an event handler


4. GUESSING that you want D-V changed in the row where C is. This clears D-V regardless of the contents of the corresponding C



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

If Intersect(Target, Range("C4:C103")) Is Nothing Then Exit Sub


Application.EnableEvents = False
For Each r In Intersect(Target, Range("C4:C103")).Cells
r.Offset(0, 1).Resize(1, 19).ClearContents
Next
Application.EnableEvents = True
End Sub

[j.stitt1]

this is why.
it may be incorrect but it seems to works.


If Range("C4") = "" Then Range("D4:V4").ClearContents (With is one if i use the delete key it clears my range but if i change the value of C4 it won't clear the range.)
If Range("C4") <> "" Then Range("D4:V4").ClearContents (With is one if i use the if i change the value of C4 it clears the range but if i use delete key it won't clear my range.)

[j.stitt1]

Thank You!

but i would like to add a cell to clear. I see you are clearing 1 thru 19 cells after column C i also need to skip 20, 21 and clear 22.

how would i add?

[p45cal]

either add directly after the
r.Offset(0, 1).Resize(1, 19).ClearContents
line, the following:
r.Offset(, 22).ClearContents

or
replace it with:
Union(r.Offset(, 1).Resize(, 19), r.Offset(, 22)).ClearContents

[j.stitt1]

Awesome!!
Thank You!