Blank a cell if the contents of another cell deleted

girapas

Board Regular
Joined
Apr 20, 2004
Messages
150
Say that in every cell (A1 to A10) of a sheet, I select an item with Data Validation / List. The list is a named one (say 'List1').
After each selection I enter data (only numbers) to the adjacent cells (the same row, columns B to D). What I want is when I delete the content of cells B1 to D10, the value in cells A1 to A10 to be automatically blank. I've tried in another column (L) to enter formulas like these without success:
=IF(B1=0,$A1="")
=IF(B1=0,$A1=0)
=IF(ISBLANK(B1),$A1="")

Notice that I cannot use Conditional Formatting because I've used all Conditions in the whole range A1:D10.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Cloverken

Active Member
Joined
Feb 18, 2002
Messages
271
An Excel Function cannot perform an action like clearing the contents of another cell. However you can use the Worksheet_Change event with Visual Basic to perform what you need. Simply copy the following code and open your worksheet then press Alt+F11 and paste the code in the Right-Side open pane. Then close the Visual Basic Editor and test it out.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
      Dim RangeA As Range
      Set RangeA = Range("B1:D10")
        On Error Resume Next
            If Application.CountBlank(RangeA) = RangeA.Cells.Count Then
                Range("A1:A10").ClearContents
            Else
                Exit Sub
            End If
      Set RangeA = Nothing
End Sub
 
Last edited:

Forum statistics

Threads
1,144,582
Messages
5,725,119
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top