Have Geek Brain

How can I tell if a cell in Excel contains a number / text or a formula?

What I like to do is conditionally format the cells depending on whether the cell contains a formula (something starting with "=") or not.

Is this possible in Excel 2007?

+2


source to share


4 answers


You can use:

If (cell.HasFormula) then ..


Search for formulas.

(This will avoid the problem of a literal string field starting with "=")

+3


source


AFAIK, there is no worksheet function or conditional validation available for formulas. VBA offers a method range.HasFormula

that returns true if every cell in the range (which can be a single cell) has a formula.



+3


source


Just for reference (and for upvoting :-)), I finally solved auto coloring with VBA without using conditional formatting:

dim ws as Worksheet
for each ws in thisworkbook.sheets
  ws.Cells.SpecialCells(xlCellTypeFormulas).Font.ThemeColor = xlThemeColorAccent1
next ws

works great for me. (Found here )

+1


source


I realize it has been inactive for over a year, but given recurring comments about the hope of avoiding VBA I, even though it was worth posting XLM using the Range Names approach.

based on code by David Hager from http://j-walk.com/ss/excel/eee/eee003.txt

  • Determine the name of the range. IsFormula refers to: = GET.CELL (48, INDIRECT ("rc", FALSE))
  • Add a conditional formatting rule "using a formula to determine which cells to format" = IsFormula in the range of interest and choose a color

And you're done

The historical advantage of using XLM was that it allowed VBA code to be coded without providing macros. The presence of XLM now contains a warning about such content, but it is still useful to use these XLM conditional formatting tricks without having to trigger a VBA event in the background.

I wrote a longer article on using XLM / Range Names / Conditional Formmating to define external cell references, page references, formulas, etc. and the inconsistent formulas at http://www.experts-exchange.com/A_7629.html (note that this is outside of the EE payment system, it's freely available without participation)

+1


source






More articles:


All Articles