Multiple Events not displayed for a day in the Calendar
Dear Experts
I am enclosing herewith the Holiday and To Do List Calendar which shows only a single event for a day. I request you to help me in showing multiple events, in case if there is more than one per day / date.
Re: Multiple Events not displayed for a day in the Calendar
This code is placed in worksheet_change event, to trigger change in B2
PHP Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim k&, i&, t&, cell As Range, arr(1 To 1000, 1 To 2)
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
Range("A5:G40").ClearContents ' delete history
'read event date into array (arr)
With Worksheets("Sheet2")
For Each cell In .Range("F2:F" & .Cells(Rows.Count, "F").End(xlUp).Row)
k = k + 1
arr(k, 1) = cell.Value
arr(k, 2) = cell.Offset(0, 1).Value
Next
For Each cell In .Range("J2:J" & .Cells(Rows.Count, "J").End(xlUp).Row)
k = k + 1
arr(k, 1) = cell.Value
arr(k, 2) = cell.Offset(0, 1).Value
Next
End With
k = 0
For Each cell In Range("A5:G5, A11:G11, A17:G17, A23:G23, A29:G29, A35:G35")
cell.Value = Target - Choose(Weekday(Target), 0, 1, 2, 3, 4, 5, 6) + k ' find the latest Sunday of month, write to first cell
If cell.Row = 5 Then cell.Offset(-1, 0).Value = Format(cell, "ddd") ' days header
k = k + 1
' loop through event dates, if found then write into sheet, row by row
For i = 1 To UBound(arr)
If cell.Value = arr(i, 1) Then
t = t + 1
cell.Offset(t, 0).Value = arr(i, 2)
End If
Next
t = 0
Next
End Sub
Bookmarks