IF function does not populate when condition met

Regular Contributor

Would like to know what is wrong with the IF function for the code below as it does not populate the expected text "waive admin fee" when condition is met..


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws          As Worksheet
    Dim i           As Long
    Dim lastRow     As Long
    Set ws = ThisWorkbook.Sheets("Complete (new format)")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Application.EnableEvents = FALSE
    On Error Resume Next
    For i = 2 To lastRow
        If ws.Range("K" & i).Value > 0 And ws.Range("K" & i).Value <= 10 Then
            ws.Range("L" & i).Value = "waive admin fee"
        End If
    Next i
    Application.EnableEvents = TRUE
End Sub


thanks for the assistance in advance..

4 Replies


This is an event procedure for the Worksheet_Change event.

  1. Do you want it to react to changes in column K?
  2. If so, does the user edit column K, or does column K contain formulas?

1. Yes, i would like to react to changes in column K and
2. Column K does not contain formulas just data
best response confirmed by hrh_dash (Regular Contributor)


Wouldn't it be easier to enter the following formula in L2 and fill down?

=IF(AND(K2>0,K2<=10),"waive admin fee","")

If you prefer the event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("K2:K" & Rows.Count), Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(Range("K2:K" & Rows.Count), Target)
            If rng.Value > 0 And rng.Value <= 10 Then
                rng.Offset(0, 1).Value = "waive admin fee"
                rng.Offset(0, 1).ClearContents
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub


,thanks and appreciate the help!