I only want code to run if range that is blank to start with has any input entered, right now it runs any time change is made

Question

Private Sub Worksheet_Change(ByVal Target As Range)

StartRow = 21
EndRow = 118
ColNum = 1
For i = StartRow To EndRow
If Cells(i, ColNum).Value = Range("A4").Value Then
Cells(i, ColNum).EntireRow.Hidden = True
Else
Cells(i, ColNum).EntireRow.Hidden = False
End If
Next i

End Sub

The Range I want to dictate when the code is run is D21:D118. It will start out blank and then have data pulled into it

Thank you!

Answer 1

It's quite difficult and error-prone to tell in a Change event handler what the previous cell value was before it was edited. You might consider narrowing the logic so it only runs if a cell in A21:A118 is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, c As Range, vA4
    'Does Target intersect with our range of interest?
    Set rng = Application.Intersect(Target, Me.Range("A21:A118"))
    If rng Is Nothing Then Exit Sub 'no change in monitored range
    
    vA4 = Me.Range("A4").Value
    For Each c In rng.Cells                  'loop over updated cells
        c.EntireRow.Hidden = (c.Value = vA4) 'check each updated cell value
    Next c
End Sub