Clear cells based on value in other cell

[matthijsm]

Hello to all,

I am kind of new to the use of Excel and VBA. I'm making a sheet where the employees fill in theire data. In this case it's a sheet to monitor customer contacts.

When they've reached a client by phone they have to use the excelsheet. There are a few cells they need to fill:

Reached client: Yes/No
number of calls needed for the contact
Time of contact
etc
etc

In order to correctly process all the data the cells next to "reached client" need to be cleared when there was no contact.

To prevent Excel clearing the cells over and over again I add a argument that the cell next to "reached client" need to be > 0. The code I use is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("A1") = "No" And Range("B1") > 0 Then
Range("B1:E1").Select
Selection.Clear
End If
End Sub

This was on a testsheet where A1 is Client Reached and B1:E1 are the other cells.

This code does it job perfectly, but I don't want to rewrite it for every row. Is it possible to use this code easy for the 19 rows below?

 

[BrandonWLH]

Define the rows with a named reference.

Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2
Dim InterSectRange As Range
On Error GoTo errorhandler:
    Set InterSectRange = Application.Intersect(Range1, Range2)
    InRange = Not InterSectRange Is Nothing
    Set InterSectRange = Nothing
 
    GoTo endsub:
 
errorhandler:
    On Error GoTo 0
    InRange = False
 
endsub:
 
End Function

Then use InRange to see if your ActiveCell is within that named range, if it is, then check the value of ActiveCell.