VBA recognizing more than one cell is highlighted

Question

I have the following VBA script:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False

    If Not Application.Intersect(Target, Range("calendar")) Is Nothing Then
       [selectedCell1] = ActiveCell.Value

Application.ScreenUpdating = True

    End If
End Sub

Currently, It recognizes only one cell is highlighted and returns it into the specific cell named selectedCell1.

This is my example:

If I select the cell N25 which contains the date "03/08/2017" it returns "03/08/2017" into another sheet cell named "selectedCell1".

But what I would like it to do, is realize I've selected the entire week, and then return that entire week range in cell "selectedCell1". See:

And then return 01/08/2017 - 05/08/2017 (that entire range) in cell "selecetedCell1".

Not sure how to adjust this VBA script. Help would be appreciated. Thanks.

[selectedCell1].Resize(Target.Rows.Count, Target.Columns.Count) = Target.Value Don't use ActiveCell :) — David Zemens
– David Zemens, Commented Jul 31, 2017 at 18:21

Scott Craner · Accepted Answer · 2017-07-31 18:22:57Z

1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False

    If Not Application.Intersect(Target, Range("calendar")) Is Nothing Then
        If Target.Cells.Count = 1 Then
            [selectedCell1] = Target.Value
        Else
            [selectedCell1] = Format(Application.WorksheetFunction.Min(Target), "dd/mm/yyyy") & " - " & Format(Application.WorksheetFunction.Max(Target), "dd/mm/yyyy")
        End If

Application.ScreenUpdating = True

End Sub

answered Jul 31, 2017 at 18:22

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA recognizing more than one cell is highlighted

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related