emptying the contets of a cell base on the value of another cell

T

tracktor

I am trying to empty a cells content based on the value of another cell.
Example: if cell A1 is "X" and cell B1 is "X" , and the contents in cell A1
is changed to "Y" then I want cell B1 to be emptied.
 
R

Rick Rothstein \(MVP - VB\)

Then you will need to use VBA event code. Right-click the tab for worksheet
where you want this functionality to occur on and select View Code from the
popup menu that appears; then copy/paste the following into the code window
that appeared...

'********************** START OF CODE **********************
Dim PreviousValue As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Range("A1").Value = "Y" And Range("B1").Value = "X" And _
PreviousValue = "X" Then Range("B1").Value = ""
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then PreviousValue = Target.Value
End Sub
'*********************** END OF CODE ***********************

This code will react in **exactly** the way you asked for it to react. My
gut feeling is you really want something slightly different than this, but
if that is the case, you will have to provide a more detailed description.

Rick
 
T

tracktor

Rick,

Thanks, that did it.

Rick Rothstein (MVP - VB) said:
Then you will need to use VBA event code. Right-click the tab for worksheet
where you want this functionality to occur on and select View Code from the
popup menu that appears; then copy/paste the following into the code window
that appeared...

'********************** START OF CODE **********************
Dim PreviousValue As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Range("A1").Value = "Y" And Range("B1").Value = "X" And _
PreviousValue = "X" Then Range("B1").Value = ""
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then PreviousValue = Target.Value
End Sub
'*********************** END OF CODE ***********************

This code will react in **exactly** the way you asked for it to react. My
gut feeling is you really want something slightly different than this, but
if that is the case, you will have to provide a more detailed description.

Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top