Excel VBA Worksheet_Change

[MaxMiller]

I am trying to automatically change values by a percentage when they are entered based on criteria contained in a previous row.

I have tried to do this using the following code:

Public Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("I14:I44")) Is Nothing Then
With Target
If IsNumeric(.Value) Then
.Value = .Value * 1.08
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Problem is I want to calculate a different percentage based on the values of "E14:E44" which are bound by the following list: "LTL", "Truckload", "Tank Truck" & "IM". I think some sort of Vlookup would be ideal but not sure if that can be incorporated into Worksheet_change.

Any help is greatly appreciated!

 

[Tom Urtis]

You can only declare a same-named variable once in the same macro or procedure. So delete it out of each of the 2 places where you have it now, and for simplicity's sake, have that statement, which is
Dim pctVal As Double
be the very first statement in the SheetChange event, directly below and after
Public Sub Worksheet_Change(ByVal Target As Range)

 

[MaxMiller]

I am having trouble with modifying the code to expand the functionality to column J & K.

If Intersect(Target, Range("I14:I44")) Is Nothing _
Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) = True Then Exit Sub
</pre>The code is triggering exiting the sub before I can get the to the next range... is there some sort of if then go to next if statement instead of exit sub?