A change in one cell causes a blank in another cell

[DashboardNovice]

On Sheet1 I want to do the following:

1. If the value in H4 changes, I4 should be set to blank.
2. If the value in I4 changes, H4 should be set to blank.

The idea is that at any given time, H4 or I4 can have a value. Both cells cannot have a value at the same time. It's one or the other.

Here is the code I tried.

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target = Range("H4") Then
       
        Range("I4").Value = ""
       
    Else
   
        If Target = Range("I4") Then
           
            Range("H4").Value = ""
           
        End If
   
   
    End If

End Sub

 

[AlanSidman]

What happens when you run the code. You have only showed us your code but have not told us anything.

 

[NARAYANK991]

Hi ,

Try this :

Private Sub Worksheet_Change(ByVal Target As Range)
            If Application.Intersect(Target, Union([H4], [I4])) Is Nothing Then Exit Sub
         
            If Target <> vbNullString Then
               Application.EnableEvents = False
           
               If Not Application.Intersect(Target, [I4]) Is Nothing Then [H4] = vbNullString
                                                 
               If Not Application.Intersect(Target, [H4]) Is Nothing Then [I4] = vbNullString

               Application.EnableEvents = True
            End If
End Sub

Narayan

 

[DashboardNovice]

Hi ,

Try this :

Private Sub Worksheet_Change(ByVal Target As Range)
            If Application.Intersect(Target, Union([H4], [I4])) Is Nothing Then Exit Sub
        
            If Target <> vbNullString Then
               Application.EnableEvents = False
          
               If Not Application.Intersect(Target, [I4]) Is Nothing Then [H4] = vbNullString
                                                
               If Not Application.Intersect(Target, [H4]) Is Nothing Then [I4] = vbNullString

               Application.EnableEvents = True
            End If
End Sub

Narayan

Amazing. This was perfect. Works exactly like I was trying to do. As always, thank you sir.