在VBA中使用回圈加速單元格格式化-有解無憂

我設定了一個小的 vba 宏，它回圈遍歷選定的用戶范圍并更改單元格的字體。但是，當我不小心按 Ctrl A 并用我的代碼更改字體時，整個作業表都會崩潰（我相信是因為回圈的單元格太多）。

有沒有辦法加快回圈或提高效率？

Public Sub Font_OnAction( _
   ByRef Control As Office.IRibbonControl, _
   ByRef galleryID As String, _
   ByRef selectedIndex As Integer)
   
Dim myRange As Range
Set myRange = Selection
Dim myCell As Range
   
For Each myCell In myRange

Select Case selectedIndex

    Case 0
    With myCell.Font
        .Color = RGB(3, 3, 3)
     End With

    Case 1
    With myCell.Font
        .Color = RGB(5, 5, 5)
    End With
    
    Case 2
    With myCell.Font
        .Color = RGB(10, 10, 10)
    End With
    
    Case 3
    With myCell.Font
        .Color = RGB(50, 254, 225)
    End With
    
    Case 4
    With myCell.Font
        .Color = RGB(57, 69, 251)
    End With
    
    Case 5
    With myCell.Font
        .Color = RGB(154, 154, 154)
    End With
    
    Case 6
    With myCell.Font
        .Color = RGB(228, 228, 228)
    End With
    
    Case 7
    With myCell.Font
        .Color = RGB(62, 0, 175)
    End With
    
    Case 8
    With myCell.Font
        .Color = RGB(73, 252, 156)
    End With
    

End Select

Next myCell
   
End Sub

uj5u.com熱心網友回復：

您不需要回圈：

If TypeOf Selection Is Range Then
    Select Case selectedIndex
        Case 0
            Selection.Font.Color = RGB(3, 3, 3)
        Case 1
            Selection.Font.Color = RGB(5, 5, 5) 
        Case 2
            Selection.Font.Color = RGB(10, 10, 10)
        Case 3
            Selection.Font.Color = RGB(50, 254, 225) 
        Case 4
            Selection.Font.Color = RGB(57, 69, 251)
        Case 5
            Selection.Font.Color = RGB(154, 154, 154)
        Case 6
            Selection.Font.Color = RGB(228, 228, 228)
        Case 7
            Selection.Font.Color = RGB(62, 0, 175)
        Case 8
            Selection.Font.Color = RGB(73, 252, 156)
    End Select
End If

uj5u.com熱心網友回復：

如果理解正確， selectedIndex 將定義所選范圍的顏色，因此您可以先在變數中設定顏色，然后將其應用于一行中的范圍，無需回圈。

Public Sub Font_OnAction( _
   ByRef Control As Office.IRibbonControl, _
   ByRef galleryID As String, _
   ByRef selectedIndex As Integer)

Dim myRange As Range
Dim vColor As Variant

    Application.ScreenUpdating = False
    Set myRange = Selection
    Select Case selectedIndex
        Case 0: vColor = RGB(3, 3, 3)
        Case 1: vColor = RGB(5, 5, 5)
        Case 2: vColor = RGB(10, 10, 10)
        Case 3: vColor = RGB(50, 254, 225)
        ' OTHER CASES...
    End Select
    myRange.Font.Color = vColor
    Application.ScreenUpdating = True
End Sub

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/528563.html

標籤：擅长vba

上一篇：PowerShell，告訴腳本將所有錯誤輸出到文本檔案

下一篇：ExcelEvaluate()withINDEX(range,ROW(),1)使用錯誤的行