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
Clear Cell based on Content of another cell
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 12623
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Clear Cell based on Content of another cell
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
The easiest way to do this would be a formula in F16 rather than a value that you clear
StuartR
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Clear Cell based on Content of another cell
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
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
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Clear Cell based on Content of another cell
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:
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:
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
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
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Clear Cell based on Content of another cell
The disadvantage of this code is that the formula won't come back if the user removes the date from column G.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Clear Cell based on Content of another cell
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
thanks for the quick answer.
Brad
-
- Administrator
- Posts: 12623
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Clear Cell based on Content of another cell
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.
=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.
StuartR