Discussion on:

Create an Excel function to highlight formula cells in a worksheet

Does this routine work for Excel 2003?

If your only purpose is to see which cells contain formulas, Excel 2007 has an easier way than color-coding the cells. On the Formula Tab, in the Formula Auditing group, there is a command for Show Formulas. It's a toggle button - click once it will show you the formulas in the cells, click again and it will show the cells values instead of the formulas.

Don't we first have to select some cells before adding the conditional formatting? Like all the cells, or all the used cells, on the sheet?

There is an alternate method built into Excel to achieve the same thing. Edit menu, go to, special then select formulas radio button. All formulas in the worksheet are selected and you can apply a colour from the formating toolbar. This is a lot more steps though.

Hi Mary Ann -

Thank you for this post and for the VBA Code!

When I teach an Excel Class, I always demonstrate several ways to Show / Highlight the cells that contain formulas.

I use the Ctrl+ ~ Keyboard Shortcut so that you can actually see each formula in the worksheet. This is a "Toggle" so it is easy to restore the normal view of the worksheet.

When I want to demonstrate how easy it is to have "hard-coded values" in a cell that should contain a Formula, I use Edit - Go To (or Ctrl+G or F5) and then select Special - Formulas (or Constants)

Highlighting the Formulas in a worksheet is a great way to have them selected and then apply either formatting of a Cell Style.

If you want to watch a short video on this topic, here is the link to the one that I created:

http://www.thecompanyrocks.com/excels/2008/07/how-to-reveal-the-formulas-in-all-cells/

Thanks again!

Danny Rocks
www.thecompanyrocks.com