Set cell value to value of target cell when target cell selected

[dukeofscouts]

I have a workbook that I use to check dates with. Right now the Workbook will generate a calander view of the date, along with the week before and the week after. What I'd like to add is to have excel update my date entry cell ("A2") when ever I select a cell in the calander cells ("e2:k4"). Obviosly I need somthing working with worksheet_change but I'm not sure how to go about this. Any Ideas?

 

[Zack Barresse]

Hi there,

I'm assuming this is a selection change event, not just a change event. It sounds like you want code to fire when you select, but at the end you said just a change event. I'm going to assume it's a selection event you're looking for. I think this should work...

Option Explicit

Const sUpdateRange As String = "E2:K4"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range(sUpdateRange)) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Me.Range("A2").Value = Target.Value
    Application.EnableEvents = True
End Sub

HTH

 

[dukeofscouts]

Way cool, thanks, this will make planning so much faster!