Clear Cell based on Content of another cell

[bradjedis]

Greetings all,

Here is what I would like to do.

If a Cell contains a date, then the contents of another cell needs to be deleted.

Example:

F16 contains data. IF G16 is populated with a date, then I want the contents of F16 deleted.

Conditional formatting or macro? Any assistance is greatly appreciated,

Thanks,
Brad

[StuartR]

What does the F16 contain if G16 is not a date?
The easiest way to do this would be a formula in F16 rather than a value that you clear

[bradjedis]

F16 is a formula : =D88+7

This is there regardless if G16 is blank or has a date.

It calcs a date based upon another date. Once the G16 gets populated, I would like F16 to be purged, once G16 is populated.

Thks
Brad

[HansV]

You need code for this (conditional formatting can hide cell contents, but not delete them).

Right-click the sheet tab.
Select 'View Code' from the popup menu.
Enter or copy/paste the following code into the module window that appears:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Range("G16"), Target) Is Nothing Then
    If IsDate(Range("G16").Value) Then
      Range("F16").ClearContents
    End If
  End If
End Sub

If you want to do this for a range of cells, say if a date is entered in any of the cells in the range G2:G25, the corresponding cell in F2:F25 needs to be cleared, you can loop:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim oCell As Range
  If Not Intersect(Range("G2:G25"), Target) Is Nothing Then
    For Each oCell In Intersect(Range("G2:G25"), Target).Cells
      If IsDate(oCell.Value) Then
        oCell.Offset(0, -1).ClearContents
      End If
    Next oCell
  End If
End Sub

[HansV]

The disadvantage of this code is that the formula won't come back if the user removes the date from column G.

[bradjedis]

The looping one works fine. We are not concerned with the contents of the deleted stuff, as the date tells us what is needed.


thanks for the quick answer.

Brad

[StuartR]

You can do this without code.

=IF(ISNUMBER(G16),"",D88+7)

The disadvantage of this is that any number in G16 will count as a date, but the advantage is that you don't have a worksheet_change event and therefore undo will still work on your worksheet. Also if someone puts a date in G16 and then deletes it again you will have the correct value.