Format Cycle

[bkanne]

I am trying to create a macro to cycle through several through the following number formats:

#,##0.0_);(#,##0.0);-_)
$#,##0.0_);($#,##0.0);-_)
#,##0.0%_);(#,##0.0%)
"#,##0.0x_);(#,##0.0x)

I have similar code for font colors, which is below:

Sub CycleColor()

Dim Color1 As Long, Color2 As Long
Dim Color3 As Long, Color4 As Long

Color1 = RGB(0, 0, 0)
Color2 = RGB(0, 0, 255)
Color3 = RGB(0, 128, 0)
Color4 = RGB(255, 0, 0)

With Selection.Font
    If .Color = Color1 Then
        .Color = Color2
    ElseIf .Color = Color2 Then
        .Color = Color3
    ElseIf .Color = Color3 Then
        .Color = Color4
    ElseIf .Color = Color4 Then
        .Color = Color1
    Else
        .Color = Color1
    End If

End With

End Sub

Can someone please help me write the same type of code, but for the number formats I have listed above? I tried but was unable to use similar syntax to make it work. Would be very much appreciated!

Thank you so much!

 

[NARAYANK991]

Hi ,

Try this :

Sub CycleFormat()
    Dim Format1 As String, Format2 As String
    Dim Format3 As String, Format4 As String

    Format1 = "#,##0.0_);(#,##0.0);-_)"
    Format2 = "$#,##0.0_);($#,##0.0);-_)"
    Format3 = "#,##0.0%_);(#,##0.0%)"
    Format4 = "#,##0.0x_);(#,##0.0x)"

    With Selection
         If .NumberFormat = Format1 Then
            .NumberFormat = Format2
         ElseIf .NumberFormat = Format2 Then
            .NumberFormat = Format3
         ElseIf .NumberFormat = Format3 Then
            .NumberFormat = Format4
         ElseIf .NumberFormat = Format4 Then
            .NumberFormat = Format1
         Else
            .NumberFormat = Format1
         End If
    End With
End Sub

Narayan

 

[Marc L]

My 2 cents of the day :​

Sub CycleFormat()
    F = [{"#,##0.0_);(#,##0.0);-_)","$#,##0.0_);($#,##0.0);-_)","#,##0.0%_);(#,##0.0%)","#,##0.0x_);(#,##0.0x)","#,##0.0_);(#,##0.0);-_)"}]
    V = Application.Match(Selection.NumberFormat, F, 0)
    If IsError(V) Then V = 0
    Selection.NumberFormat = F(V + 1)
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

Twice 2 cents of the day :​

Sub CycleColor()
    C = [{0,16711680,32768,255,0}]
    V = Application.Match(Selection.Font.Color, C, 0)
    If IsError(V) Then V = 0
    Selection.Font.Color = C(V + 1)
End Sub

You may Like it !
​

 

[bkanne]

You guys are incredible! Thank you so very much.

 

[bkanne]

My 2 cents of the day :​

Sub CycleFormat()
    F = [{"#,##0.0_);(#,##0.0);-_)","$#,##0.0_);($#,##0.0);-_)","#,##0.0%_);(#,##0.0%)","#,##0.0x_);(#,##0.0x)","#,##0.0_);(#,##0.0);-_)"}]
    V = Application.Match(Selection.NumberFormat, F, 0)
    If IsError(V) Then V = 0
    Selection.NumberFormat = F(V + 1)
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

How did you calculate the

Twice 2 cents of the day :​

Sub CycleColor()
    C = [{0,16711680,32768,255,0}]
    V = Application.Match(Selection.Font.Color, C, 0)
    If IsError(V) Then V = 0
    Selection.Font.Color = C(V + 1)
End Sub

You may Like it !
​

How did you calculate the colors in the C = [{0,16711680,32768,255,0}] part of the code? Start with an RGB, how do you get there?

Thank you!

 

[Chihiro]

RGB function converts 3 color values into long format....
So if you do something like below.

Sub Test()
Dim x As Long
x = RGB(0, 0, 255)
Debug.Print x
End Sub

You will see that it returns 16711680 in immediate window.

Edit: To give more detail...
Blue color value * 65536 + Green color value * 256 + Red color value = ColorIndex

 

[Marc L]

How did you calculate the colors in the C = [{0,16711680,32768,255,0}] part of the code?

I just calculate RGB function directly in Immediate VBE window … (Ctrl + G)
​

 

[bkanne]

Since you guys are kings of this and have been so helpful, I have a few additional cycle requests I hope you can provide some additional guidance on.

Similar to how the above code cycles through formats and colors, I need code for the following cycles:

1) Center Cycle

Starting with normal alignment, selection will cycle from normal to center, to center across selection, back to normal

2) Increase Decimal Cycle

Increases the decimal by one each time the macro is run

3) Decrease Decimal Cycle

Decrease the decimal by one each time the macro is run

Can't thank you enough.

 

[bkanne]

Last one - sorry:

Need an Underline Cycle as well:

This one goes from normal (no underline) to regular underline, to single accounting underline, back to normal underline.


Thank you guys!!!