Finally sorted a sub 3 second method of updating comments on calendar overview
Option Explicit
Sub comment()
Dim MyArr As Variant
Dim i, ii, A As Variant
Dim StartTime As Double
Dim SecondsElapsed As Double
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlManual
StartTime = Timer
MyArr = Sheet7.Range("c39:e62")
Set ws = Sheet7
ws.Cells.ClearComments
For ii = LBound(MyArr) To UBound(MyArr)
For i = 1 To 31
On Error Resume Next
If Cells(ii + 3, i + 2).Interior.ColorIndex = 16 Then GoTo 0
Cells(ii + 3, i + 2).AddComment
Cells(ii + 3, i + 2).comment.Text Text:=Sheets("Admin 2019 Calendar").Cells(i + MyArr(ii, 2), ((2 * MyArr(ii, 1)) - MyArr(ii, 3))).Text
Cells(ii + 3, i + 2).comment.Shape.TextFrame.AutoSize = True
Cells(ii + 3, i + 2).comment.Shape.TextFrame.Characters.Font.Size = 12
0:
Next i
Next
SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
Bookmarks