Speed up With loop in VBA for cell formatting-CodePudding

I set up a small vba macro that loops through a selected user range and changes the font of the cell. However, when I accidentally press Ctrl A and change the font with my code the whole sheet crashes (I believe because it's too many cells to loop through).

Is there a way to speed up the loop or make it more efficient?

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

CodePudding user response：

You shouldn't need to loop:

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

CodePudding user response：

If understood correctly, selectedIndex will define the selected range color, so you can set the color first in a variable, then apply it on the range in one line, no need for the loop.

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