Help with Case Selection changing number formats

[moliver]

Hi Forum,

I created a case selection to change number format by pressing at shortcut so the selection will change from Comma to Percent to Currency and then back to Comma. Code below.

The problem is when I select more than one cell and one of the cell in the selection has a different format from the other I get the following error:
"Run Time Error 91"
"Object variable or With block variable not set"
the line that I get highlighted is:

Select Case .Style

Any ideas how to fix it?

Sub Change_Format_Number()
 With Selection
    Select Case .Style
    Case "Comma"
    .Style = "Percent"
    
    Case "Percent"
    .Style = "Currency"
    
    Case "Currency"
    .Style = "Comma"
        
    Case Else
    .Style = "Comma"
    End Select
 End With
End Sub

 

[Joe4]

You need to loop through each cell in your selection.
Try this:

Sub Change_Format_Number()
 Dim cell As Range
 For Each cell In Selection
    With cell
        Select Case .Style
            Case "Comma"
                .Style = "Percent"
    
            Case "Percent"
                .Style = "Currency"
    
            Case "Currency"
                .Style = "Comma"
            
            Case Else
                .Style = "Comma"
        End Select
    End With
 Next cell
End Sub