Microsoft Office Forums - View Single Post - Clear Cell directly to right when cell to left changes value

PrincessApril · 06-26-2020, 11:56 AM

Hi all,

I'm trying to clear the cell to the right (column I, range 6-81) when the cell to the left (column H, range is 6-81) changes value.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H$6")) Is Nothing Then
        Range("I$6").ClearContents
    End If
End Sub

I have it working for the pair of cells in H6/I6, but I'm unsure how to apply the same code to the whole range so that a change in any given cell in column H will clear the data from only the cell directly to the right of it in column I (but not the whole column).

I tried using a colon I$6:I$81 and H$6:H$81 but that cleared the whole column (and I only want to clear the cell directly to the right upon a changed value to the cell directly to the left; i.e., same row). I thought I was using the dollar sign correctly but perhaps not?