Excel Cycle Font Color Command

Excel Cycle Font Color Command

Have you ever been working with text in Excel, and wondered how that text would look in a different colour? Instead of black, maybe it would be better in red, or green, or blue. Did you know that Excel has a built-in command called Cycle Font Color?

Button Images

I didn't know about Cycle Font Color, until I found it recently.

I'm building an Excel workbook that has macros, and a custom Ribbon tab, with buttons to run those macros.

One of the macros creates a list of 56 colours, so I looked for a built-in Excel command that has a colourful image.

Find a Button Image

There are tools and galleries that help you look for button images. For example, download and install this icons gallery from Microsoft:

2007 Office System Add-In: Icons Gallery

After installing the gallery, open Excel and click the Developer tab. A new group appears at the right end of the tab, with 9 galleries.

Point to any icon, to see its name. You can use that name when setting up your custom Ribbon tab.

iconsgallery02

Built-in Button Image List

Instead of using one of the icons gallery add-ins, I used Excel's build-in button image list.

  • Right-click a blank spot on the Excel Ribbon, and click Customize the Ribbon
  • In the list of commands, point to any command, to see its information
  • The image name is at the end, in brackets.

iconsgallery03

Not in the Ribbon

In the Customize the Ribbon window, the default list is Popular Commands.

To find something a bit different, I chose the list of "Commands Not in the Ribbon".

After scrolling down a bit, I spotted the type of colourful image that I wanted – Cycle Font Colors.

I jotted down its image name – FontColorCycle – to use in my custom Ribbon tab.

iconsgallery04

What Does It Do?

I didn't know what that command does, so I wanted to try it. The Cycle Font Colors command isn't on the Excel Ribbon, but you can add it to your Quick Access Toolbar, or to your Ribbon.

So, I made a new group on the Home tab, and added the command, to see what it does.

iconsgallery05

Test the Button

Each time you click on the Cycle Font Color button, it adds 1 to the Color Index number for the font colour in the active cell.

The count starts at the current Index number, and goes up to 56. Then, it starts at 1 again.

In the screen shot below, the text started as Black (color index 1). The first click changed the color index number to 2 (White), and the next click changed the font to Red.

Cycle Font Color

To see the colours a little better, you could use the Wingdings font, with a lower case "n".

iconsgallery06

Real-Life Uses?

It's entertaining to use the Cycle Font Color button, but I can't think of any real-life uses for it. Can you think of anything?

Maybe Cycle Font Color was helpful in the old days, when we had to find our way into Tools | Options | Color, to adjust the settings. Clicking a button would be a lot quicker than that!

iconsgallery07

Excel Color Trivia

Also, did you know that Excel recognizes the first 8 colours by name too?

You can use those names in custom number formatting, to change the font colour based on conditions.

In this screen shot, the numbers >=50 are red, and anything less than 50 is green.

[green][<50];[red][>=50]

custom number format with color

More Colour Info

For more colour resources, go to my Colours blog post from earlier this year.

You'll find a links to John Marshall's Color Lists Workbook, Jon Peltier's article on Excel colors, chart colour macros, and other colour references.

________________

3 thoughts on “Excel Cycle Font Color Command”

  1. I have had these two macros on my QAT for a few years, the first cycles through the 5 font colours I use most often, Blue for input cells, the others for various highlights. The second cycles through 5 cell shading (fill) colours. Together they make formatting on the fly quick, easy, and consistent from workbook to workbook.

    Sub My_FontColours()
    Dim Colour As Double
    'imageMso="SmartArtChangeColorsGallery"
    If Selection.Font.Color = 0 Then
    Colour = RGB(0, 0, 247) 'Blue
    ElseIf Selection.Font.Color = RGB(0, 0, 247) Then Colour = RGB(0, 157, 0) 'Green
    ElseIf Selection.Font.Color = RGB(0, 157, 0) Then Colour = RGB(147, 0, 0) 'DarkRed
    ElseIf Selection.Font.Color = RGB(147, 0, 0) Then Colour = RGB(187, 187, 187) 'medium grey
    Else: Colour = 0 'black
    End If
    Selection.Font.Color = Colour
    End Sub

    Sub My_CellFillColours()
    Dim Colour As Double
    'imageMso="FontColorMoreColorsDialog"
    If Selection.Interior.Pattern = xlNone Then
    Colour = RGB(237, 237, 237) 'grey
    ElseIf Selection.Interior.Color = RGB(237, 237, 237) Then Colour = RGB(255, 255, 207) 'yellow
    ElseIf Selection.Interior.Color = RGB(255, 255, 207) Then Colour = RGB(247, 255, 217) 'green
    ElseIf Selection.Interior.Color = RGB(247, 255, 217) Then Colour = RGB(237, 247, 255) 'blue
    ElseIf Selection.Interior.Color = RGB(237, 247, 255) Then Colour = RGB(247, 227, 227) 'pink
    Else: Selection.Interior.Pattern = xlNone: Exit Sub 'white
    End If

    With Selection.Interior
    .Pattern = xlSolid
    .Color = Colour
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.