选择多个单元格

Question

我有一段代码用于检查附件的大小是否大于10MB。如果附件大于10MB，则在msgbox上显示文件名，然后我想选择或突出显示具有此附件的单元格大于10 MB，但不知道如何实现。

这是我尝试过的：

Function checkAttSize()

Application.ScreenUpdating = False
Dim attach As Object
Dim attSize() As String
Dim loc() As String
Dim num As Long
Dim rng As Range

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)

Set main = ThisWorkbook.Sheets("Main")
lRow = Cells(Rows.count, 15).End(xlUp).Row
efCount = 0
num = 0
With objMail
    If lRow > 22 Then
    On Error GoTo errHandler
        For i = 23 To lRow
            'attach.Add main.Range("O" & i).value
            'totalSize = totalSize +
            If (FileLen(main.Cells(i, "O").value) / 1000000) > 10 Then
                ReDim Preserve attSize(efCount)
                ReDim Preserve loc(num)
                'store file names
                attSize(efCount) = Dir(main.Range("O" & i))
                'store cell address
                loc(num) = i
                efCount = efCount + 1
                num = num + 1
                found = True
            End If
        Next i
    End If
End With

If found = True Then
    MsgBox "Following File(s) Exceeds 10MB Attachment Size Limit:" & vbCrLf & vbCrLf & Join(attSize, vbCrLf) _
    & vbCrLf & vbCrLf & "Please try removing the file(s) and try again.", vbCritical, "File Size Exceed"
'trying to select the cell addresses
    For i = 1 To num
        rng = rng + main.Range("O" & loc(i)).Select ' Ive also tried &
    Next i
    checkAttSize = True
    Exit Function
End If
Exit Function
errHandler:
MsgBox "Unexpected Error Occured.", vbCritical, "Error"
checkAttSize = True
End Function

感谢您的帮助。

- Eem Jee

Answer 1

不需要选择范围。用户一次错误的点击就会使焦点从范围中移开。而且过度使用.Select可能会引发运行时错误。相反，可以对其进行着色。

在此行之后

If (FileLen(main.Cells(i, "O").value) / 1000000) > 10 Then

添加这行代码。

main.Cells(i, "O").Interior.ColorIndex = 3

现在这些单元格将会被涂成红色。

最后，用消息提示用户。

If found = True Then
   MsgBox "File(s) Exceeding 10MB Attachment Size Limit has been colored in red:"
End If

- Siddharth Rout