Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' Auto adjust cell size for comment fields
Dim RowHt As Single, MergeWidth As Single
Dim C As Range, AutoFitRng As Range
Dim CWidth As Single, NewRowHt As Single
Static OldRngAdd As String
Dim OldRng As Range
On Error Resume Next
If bDisableEvents Then Exit Sub
If OldRngAdd = "" Then
Set OldRng = Union(Range("CommentRange1").MergeArea, Range("CommentRange2").MergeArea)
OldRngAdd = OldRng.Address
Else
Set OldRng = Range(OldRngAdd)
End If
Set AutoFitRng = Union(Range("CommentRange1"), Range("CommentRange2"))
If Not Intersect(OldRng, AutoFitRng) Is Nothing Then
Application.ScreenUpdating = False
With OldRng
RowHt = .RowHeight
CWidth = .Cells(1).ColumnWidth
For Each C In OldRng
MergeWidth = C.ColumnWidth + MergeWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergeWidth
.EntireRow.AutoFit
NewRowHt = .RowHeight * 0.7
.Cells(1).ColumnWidth = CWidth
.MergeCells = True
.RowHeight = NewRowHt
.Locked = False
End With
Application.ScreenUpdating = True
End If
OldRngAdd = Target.Address
End Sub