how to automatically clear text if text in another cell is removed

[niygint_m]

i am a beginner at vba, and am building a code to automatically clear text in column J when text in either column H or I is removed.

i have this code already, but text in column J is cleared only when text in column I is removed.
when i tried removing text in column H, text in column J doesn't clear...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim lastRow As Long

' Check if changes occurred in columns H or I
If Not Intersect(Target, Me.Range("H:I")) Is Nothing Then
Application.EnableEvents = False ' Disable events to prevent triggering again

' Find the last row with data in column H
lastRow = Me.Cells(Me.Rows.Count, "H").End(xlUp).Row

' Loop through each row to calculate the difference
For i = 1 To lastRow

' Clear the result in J if either H or I is empty
If IsEmpty(Me.Cells(i, "H").Value) Or IsEmpty(Me.Cells(i, "I").Value) Then
Me.Cells(i, "J").ClearContents

Else

' Check if both H and I have numeric values
If IsNumeric(Me.Cells(i, "H").Value) And IsNumeric(Me.Cells(i, "I").Value) Then
Me.Cells(i, "J").Value = Me.Cells(i, "H").Value - Me.Cells(i, "I").Value
Else
Me.Cells(i, "J").ClearContents ' Clear if not numeric

End If
End If
Next i
Application.EnableEvents = True ' Re-enable events
End If
End Sub

 

[Joe4]

I think you are overcomplicating it. You only need to check the row(s) that were just updated, not the whole range.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("H:I"))
    
'   See if any cells updated in columns H or I
    If Not rng Is Nothing Then
        Application.EnableEvents = False
'       Loop through updated cells only
        For Each cell In rng
'           Check to see if updated cell is empty
            If cell = "" Then
'               Remove value from column J
                Cells(cell.Row, "J").ClearContents
            End If
        Next cell
        Application.EnableEvents = True
    End If
    
End Sub