Clear range if conditional cell is blank

[JJM0512]

Hi

I am trying to find a way of automatically clearing the contents of columns C:AT in row x if the user manually clears the contents in column B in row x.

I have found a way of doing it for the first row in my sheet (row 3), however I would like to replicate this for the succeeding 100 rows. The following VBA code works for row 3...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("B3")) Is Nothing Then
        Range("C3:AT3").ClearContents
    End If
End Sub

I must also point out that this code succeeds the follwing VBA code...

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

    For Each p In Range("A1:At1").Cells
        If p.Value = "N" Then
            p.EntireColumn.Hidden = True
        ElseIf p.Value = "Y" Then
            p.EntireColumn.Hidden = False

        End If
    Next p
End Sub

So it would need to be compatible with this one too.

Hope someone can help!

Thanks
Joe

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[JJM0512]

Your requirement is on column B. Try again but deleting data in column B, you can delete multiple cells at the same time.


Try this

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, rng2 As Range, c As Range, p as range
 
  Set rng = Intersect(Target, Range("B3:B" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      If c.Value = "" Then
        Application.EnableEvents = False
        Range("C" & c.Row & ":AT" & c.Row).ClearContents
        Application.EnableEvents = True
      End If
    Next
  End If
 
   For Each p In Range("A1:At1").Cells
        If p.Value = "N" Then
            p.EntireColumn.Hidden = True
        ElseIf p.Value = "Y" Then
            p.EntireColumn.Hidden = False
        End If
    Next p
End Sub

Hero #2