How can I blank out a cell if there is a value in a different cell

[Otter]

I have cells A3, A4,A5, & A6. I want to blank out A3,A4,A5 if there is a
value entered into A6. or I want to blank out A3 & A6 and force entry in A4
if something is entered in A5 or blank out A3,A5,A6 if something is in A4 or
blank out A4,A5,A6 if something is entered in A3. Your help is much
appreciated.

Thanks

A3 = Dept
A4 = Class
A5 = Subclass
A6 = Item

 

[Bernie Deitrick]

Otter,

Copy the macro below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A3:A6")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Address = "$A$6" Then
Range("A3:A5").ClearContents
End If
If Target.Address = "$A$5" Then
Range("A3,A4,A6").ClearContents
Range("A4").Value = InputBox("What goes into cell A4?")
End If
If Target.Address = "$A$4" Then
Range("A3,A5,A6").ClearContents
End If
If Target.Address = "$A$3" Then
Range("A4:A6").ClearContents
End If
Application.EnableEvents = True
End Sub

 

[Otter]

That worked perfect.

Thanks

Otter,

Copy the macro below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A3:A6")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Address = "$A$6" Then
Range("A3:A5").ClearContents
End If
If Target.Address = "$A$5" Then
Range("A3,A4,A6").ClearContents
Range("A4").Value = InputBox("What goes into cell A4?")
End If
If Target.Address = "$A$4" Then
Range("A3,A5,A6").ClearContents
End If
If Target.Address = "$A$3" Then
Range("A4:A6").ClearContents
End If
Application.EnableEvents = True
End Sub