Conditional formatting to highlight cells with formulas (vs. constants)

**6StringJazzer** · 11-24-2010, 03:58 PM

Is it possible to use conditional formatting to highlight cells that use formulas, as opposed to having constants?

I have a sheet that uses formulas to provide a default value, but you can type in a number if you want to replace the default. I want to be able to easily identify which ones use the default formula.

If I use functions like FIND, they look at the result of a formula, and not the formula itself.

I know I can write a UDF that will figure it out but I was wondering if there is some built-in way.

**ConneXionLost** · 11-24-2010, 04:12 PM

Hi,

Please Login or Register  to view this content.

Cheers,

**Maistrye** · 11-24-2010, 04:28 PM

Hi,

If you highlight the section of the spreadsheet you want to look at, then hit the following keystrokes, it will highlight only the cells with formulas.

CTRL+G, ALT-S, F
(You can select whether you want Numbers, Text, Logicals, or Errors)

If you want to highlight cells with constant values, you can hit:

CTRL+G, ALT-S, O
(Again, you can select whether you want Numbers, Text, Logicals, or Errors)

As to where the Go To thing is in the menu paths, I haven't a clue... so the keystrokes are all I can give you.

Hope that helps.
S

**sweep** · 11-25-2010, 05:10 AM

Hi,

You can do this with XLM to avoid code

Create a named range called IsFormula that refers to

Please Login or Register  to view this content.

Use the formula

Please Login or Register  to view this content.

to activate your conditional formatting

**6StringJazzer** · 11-25-2010, 10:29 AM

Thanks sweep, that's exactly what I was hoping for. I had to look that one up--I'm completely unfamiliar with Excel 4 macros.

**DeRo22** · 04-11-2013, 10:58 AM

Can this be reversed so it highlights cells that do not have formulas? I want to see where users of a template have overriden a cell with a value.

**6StringJazzer** · 04-12-2013, 05:10 PM

Welcome to the Forum DeRo22!

Normally the moderators frown on asking a new question in an old thread. In this case I'll answer it because your question is SO closely related to the original question, which I asked myself.

Use the technique shown by sweep (if you want to avoid VBA) or ConneXionLost (if you don't mind VBA). Then for the condition use

=NOT(IsFormula)

However, this will also highlight blank cells in the affected range so make sure you set the correct range for your conditional formatting to include only those cells that you put formulas in.

**Tony Valko** · 04-12-2013, 09:21 PM

If you're using Excel 2007 or later and you choose to use the XL4 macro function GET.CELL(...) the file MUST be saved as a macro enabled file in the *.xlsm format.

So, there's really no advantage in using the XL4 macro function to avoid using the VBA UDF. Both will require saving the file as a macro enabled file in the *.xlsm format.