VBA - Change and Calculate

[LisaMalaine]

I am working on a project where I have a range of cells (AB9:AB27) and anytime there is a change in one of these cells, I want it to calculate that specific cell and the one to the right of it. I am using comments in the cell from a drop down, which work great, but because I am calculating an entire column, it is taking a long time to calculate, instead of just the cell that changes and the cell next to it. The code I have pasted below is for both the comment to populate and be formatted, but it is calculating the whole column. Any suggestions on how to do this a different way to cut down on the timing it is taking to calculate?

Also, when it calculates AB, I also want it to calculate AK - AM on that same row.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
If Target.Column >= 1 And Target.Column < 28 Or Target.Column >= 36 And Target.Column < 500 Then
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Exit Sub
Else
If Target.Column = 28 Then
Set Cmnt = Target.Comment
If Cmnt Is Nothing Then
Target.AddComment Text:=Target.Offset(0, 1).Text
Else
With Cmnt
Range("AC9:AC27").Calculate
Range("AK9:AM27").Calculate
.Text Text:=Target.Offset(0, 1).Text
End With
End If
End If
End If
If Target.Column = 30 Then
Set Cmnt = Target.Comment
If Cmnt Is Nothing Then
Target.AddComment Text:=Target.Offset(0, 1).Text
Else
With Cmnt
Range("AE9:AE27").Calculate
.Text Text:=Target.Offset(0, 1).Text
End With
End If
End If
If Target.Column = 32 Then
Set Cmnt = Target.Comment
If Cmnt Is Nothing Then
Target.AddComment Text:=Target.Offset(0, 1).Text
Else
With Cmnt
Range("AG9:AG27").Calculate
.Text Text:=Target.Offset(0, 1).Text
End With
End If
End If
If Target.Column = 34 Then
Set Cmnt = Target.Comment
If Cmnt Is Nothing Then
Target.AddComment Text:=Target.Offset(0, 1).Text
Else
With Cmnt
Range("AI9:AI27").Calculate
.Text Text:=Target.Offset(0, 1).Text
End With
End If
End If
Dim MyComments As Comment
Dim lArea As Long
For Each MyComments In ActiveSheet.Comments
With MyComments
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.TextFrame.Characters.Font.Name = "Arial"
.Shape.TextFrame.Characters.Font.Size = 8
.Shape.TextFrame.Characters.Font.ColorIndex = 2
.Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
.Shape.Line.BackColor.RGB = RGB(255, 255, 255)
.Shape.Fill.Visible = msoTrue
.Shape.Fill.ForeColor.RGB = RGB(166, 166, 166)
.Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
.Shape.Height = (lArea / 200) * 1.1
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
End If
End With
Next
End Sub

 

[JLGWhiz]

If I understand your objective correctly, I believe you could just change this

Range("AC9:AC27").Calculate
 Range("AK9:AM27").Calculate

To this

Target.Resize(1, 2).Calculate
Target.Offset(, 9).Resize(1, 3).Calculate

 

[LisaMalaine]

I am starting to wonder if it is the calculation that is causing it to slow down or if it the comment code because each time I make a change, in the right hand corner it is giving me a message that says "cell AB8 (it repeats this with different cells) commented by username". Is there a way to fix this?

 

[JLGWhiz]

I am starting to wonder if it is the calculation that is causing it to slow down or if it the comment code because each time I make a change, in the right hand corner it is giving me a message that says "cell AB8 (it repeats this with different cells) commented by username". Is there a way to fix this?

I think that is probably an Excel thing and I am not familiar with the vba syntax to do it by code, but here is a link that might help you fix it.
https://www.isunshare.com/blog/remove-or-change-comment-author-name-in-excel/

But I doubt that is the cause of the processing speed problem.