Clear Cell A (Date) based on selection from a list of values in Cell B

Sp1nster

New Member
Joined
Apr 21, 2009
Messages
2
I have read variations on this type of problem, but am still struggling to find the answer. I want to clear a date field (Cell A) when the user selects from a list of values in Cell B. In cell B, there are 6 values that require the date to clear. The other 3 Values in Cell B require the date field to remain. I believe I need a macro to accomplish this. Help would be appreciated!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Try this code, note that I used range names, "date1" for the date to be cleared (or not), "select1" for the cell containing the selection form the validation list, and "list1" for the validation list.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim List_Item As Integer
Static old_value As Variant
If ActiveSheet.Range("select1").Value <> old_value Then
List_Item = WorksheetFunction.Match(Range("select1").Value, Range("list1"), 0)
Select Case List_Item
'Note.. the following criteria is a simple example, change as necessary.
'This says if the selected item is either the first three items in the list,
'clear the date, or if it is the 6'th item in the list.
Case Is < 4
Range("date1").ClearContents
Case Is = 6
Range("date1").ClearContents
End Select
old_value = ActiveSheet.Range("select1")
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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
Back
Top