Help to get last cell of selection

[michealj46]

ew to VBA this is my first project.
I am building a calendar to track bookings.
The months are laid out in blocks ( 6 down , 2 across) of 5 rows by 7 columns.
Booking dates are highlighted by selecting with the mouse and clicking a button to set the cell fill colour.
Ideally I want to identify the first and last cells of the selection by using a different colour fill than that of the rest of the selection.
The sub I have for filling isas follows
Dim r As Range, last As Range
Set r = Selection
With r.Interior
.ColorIndex = 4
.Pattern = xlSolid
.PatternColorIndex = 1
End With
Set last = r.Cells.Item(r.Cells.Rows.Count, r.Cells.Columns.Count)
last.Interior.ColorIndex = 1
This highlights the last cell for a complete row or rows
The problem is when I have one full row and a partial second row .
The different colour ends up on end column of the last complete row.
I would apprceciate help with this and a suggestion of how to select the first cell
Thanks
Micheal

[p45cal]

How are the users selecting the dates? Using the Ctrl and Shft keys in conjunction with the mouse? I'm going to presume Yes. I played, with finding the top left cell and the bottom right one, but came across the problem of someone selecting dates crossing over from one column of months to the next column of months. I found it easier to assume one more thing; that you've got proper dates in the cells and users won't select two distinct (non-contiguous) sets of dates at once. The following macro (sorry I haven't Dimmed everything) looks for the max and min dates in the selection and makes those first and last:


Sub blah()
Dim r As Range, last As Range
Set r = Selection
With r.Interior
.ColorIndex = 4
.Pattern = xlSolid
.PatternColorIndex = 1
End With

For Each cll In r.Cells
If cll.Value = Application.WorksheetFunction.Min(Selection) Then Set first = cll
If cll.Value = Application.WorksheetFunction.Max(Selection) Then Set last = cll
Next cll
last.Interior.ColorIndex = 1
first.Interior.ColorIndex = 3
End Sub
It helps if you protect cells that don't contain dates so that users can't select them.

[Bob Phillips]

Set last = r(r.Count)

[michealj46]

p45cal,

thanks, works great.

I have protected the sheet and built in a couple of error messages if users try to select the wron cells.

Much appreciate your help

Micheal

[p45cal]

Set last = r(r.Count)

I couldn't make this work with a non-contiguous selection. It seems to treat the first area as the top row of a rectangular block and then chooses the (r.Count)th cell in that block as last, and this can be outside any current selection. I think it's likely the selections by the users will be non contiguous, especially as a booking may go from one month to another and the op says the months are in discrete blocks.