Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Cycling through Colors.
Excel provides a tool you can use to cycle through the available font colors. The tool is not available by default, but must be added by following these steps:
Figure 1. Customizing the Quick Access Toolbar.
Now, whenever you click on the Cycle Font Color tool, the font color in the selected cells will change. Click again, and the color changes again. Keep clicking, and eventually the font colors repeat.
If you cannot locate the Cycle Font Colors command in step 4, try using the Choose Command From drop-down list (step 3) to choose Commands Not In the Ribbon. For some odd reason, in some versions of Excel Microsoft made the command available in that group, but not in the All Commands group. Go figure!
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6130) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Cycling through Colors.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Want to change the size of the font within a worksheet? Excel allows you to choose from a list of sizes, as well as ...
Discover MoreOne of the most common cell alignment settings I use is the one to center across a selection. If you want to vary how ...
Discover MoreNeed to get rid of the borders around a cell? The shortcut in this tip can make quick work of this formatting task.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-02-29 19:07:45
Allen G.
Once again, Allen, you have made me think and come up with a way to do thinks better than I was before. Turns out that duplicating the color cycle in VBA is pretty easy:
dim ColorCode as long
ColorCode = Wksht.Rng.Font.ColorIndex
If ColorCode = 56 then
ColorCode = 1
End if
ColorCode = ColorCode +1
Wksht.Rng.Font.ColorIndex = ColorCode
When called by a button, it will change the font color in the target range on each click. A simple modication of this code lets you cycle in the other direction. Much more user friendly than the work-around I've been using, although I will keep my original code in as a fall-back for users since it duplicates the "More Colors" option in Excel.
2020-02-29 16:58:24
Allen G.
Is there a way to accress this option through VBA? I have a workbook that allows users to change font colors in various places on protected worksheets through VBA. I don't allow user edit except under very controlled conditions in order to minimize potential erros and bad results. However, it's a trial-and-error kind of thing requiring the user to repeat the process if the color chosen ends up not being right when applied to the cell. Is there a way to call Cycle Font Colors with VBA?
2020-02-29 10:20:54
J. Woolley
@Mark B
You should have clicked "Quick Access Toolbar" in the left panel, not "Customize Ribbon".
2020-02-28 10:52:26
Mark B
The option was not available for me in All Commands. (I think I have 2016) I did find it in Commands Not In The Ribbon. Then I was forced to create a New Group on the right side before it would let me add it. But now it works and is very useful for me. Thanks!
(see Figure 1 below) (see Figure 2 below)
Figure 1. All Commands
Figure 2. Not in Ribbon
2015-06-29 03:10:11
Prashanth
Hello Allen,
Thanks for this tip, it is helpful. How do I cycle through chart colours? For example, I have a stacked bar chart and need to cycle through them. How do I do? Thanks for the help.
Prashanth
India
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2020 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Comments