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.
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.
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.
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.
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.
To see the colours a little better, you could use the Wingdings font, with a lower case “n”.
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!
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]
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.
________________
That button might be a little more useful if it told you the current Color Index number in the hover text. Fun toy, though…
You’re right, Lynda!
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