Clearing certain cells in a new Data Table row when Dropdown List selection changes

[baseball17bucks]

Hi there!

I am fairly new to VBA and brand new to mrexcel.com so please be as detailed as possible in responses (thanks!).

My issue: I have a data table called "DataEntry" It has four columns (i.e. fields) - JobType, Rate, Hours, and TotalPayment.

• JobType is a dropdown list of choices (i.e. Clerk, Janitor, Accountant, etc.)
• Rate is a vlookup to another table
• Hours is a manual input for a user
• TotalPayment is a calculation of Rate x Hours

My goal: If someone changes their choice of JobType, I want Hours to be cleared. Notice that I still wish to maintain the formulas for Rate and TotalPayment.

Any ideas or approaches? I've found the following code which seems to be on the right track, but I'm not sure how to scale it up to perform on a growing data table.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "B10" Then Range("E10").ClearContents
End Sub

Here, B10 is the "JobType" row in my table, and E10 is the first Hours row. When I change JobType, Hours is cleared, but I don't know how to get this to work for the rest of the table.

Thanks again for your detailed response!!!

 

[ParamRay]

.
.

Place the following macro in the code module corresponding to that particular worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rang As Range
    Dim Cell As Range
    
    Set Rang = Intersect(Target, Me.Columns("B"))
    
    If Not Rang Is Nothing Then
        For Each Cell In Rang
            Application.EnableEvents = False
            Me.Cells(Cell.Row, "E").ClearContents
            Application.EnableEvents = True
        Next Cell
    End If

End Sub