如何确定Excel中的单元格是包含数字/文本还是公式？

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

我喜欢做的是根据单元格是否包含公式(以“=”开头)来有条件地格式化单元格。

Is this feasible in Excel 2007?

这在Excel 2007中是否可行?

4 个解决方案

#1

You can use:

您可以使用:

If (cell.HasFormula) then ..

To look for formulas.

寻找公式。

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

(这将避免以“=”开头的文字字符串字段的问题)

#2

AFAIK, there is no readily accessible worksheet function or conditional to test for formulae. VBA offers the range.HasFormula method that returns true if every cell in the range (which can be a single cell) has a formula.

AFAIK,没有易于访问的工作表函数或条件来测试公式。 VBA提供range.HasFormula方法,如果范围中的每个单元格(可以是单个单元格)都有公式,则返回true。

#3

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

仅供参考(以及upvoting :-)),我终于使用VBA解决了自动着色问题,而不使用条件格式:

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

works fine for me. (Found here)

对我来说很好。 (在这里找到)

#4

I realise this has been inactive for over a year but given the repeated comments about hoping to avoid VBA I though it was worth posting the XLM with Range Names approach.

我意识到这已经不活动了一年多但是考虑到一再希望避免VBA的评论我虽然值得发布带有Range Names方法的XLM。

drawing on David Hager's code from http://j-walk.com/ss/excel/eee/eee003.txt

借鉴David Hager在http://j-walk.com/ss/excel/eee/eee003.txt上的代码

Define a range name IsFormula refers to:=GET.CELL(48,INDIRECT("rc",FALSE))

定义范围名称IsFormula是指:= GET.CELL(48,INDIRECT(“rc”,FALSE))

Add a Conditional Formatting rule "using a formula to determine which cells to format" = IsFormula to the range of interest and pick your colour

添加条件格式规则“使用公式确定要格式化的单元格”= IsFormula到感兴趣的范围并选择颜色

And you are done

你完成了

The historical advantage of using XLM was that it allowed VBA like coding without providing macro warnings. The presence of XLM now flags a similar content warning but it is still useful to be able to apply these conditional formatting XLM tricks without having to trigger a VBA event in the background.

使用XLM的历史优势在于它允许VBA编码而不提供宏警告。 XLM的存在现在标记了类似的内容警告,但是能够应用这些条件格式XLM技巧而不必在后台触发VBA事件仍然很有用。

I have written a longer article on using XLM/ Range Names/Conditional Formmating to indentify external cell links, sheet links, formulae etc and inconsistent formulae at http://www.experts-exchange.com/A_7629.html (please note that this lies outside the EE paywall, it is freely accessible without membership)

我写了一篇关于使用XLM / Range Names / Conditional Formmating来识别外部单元格链接,表单链接,公式等以及http://www.experts-exchange.com/A_7629.html上不一致公式的更长文章(请注意这个位于EE paywall之外,无需会员即可免费使用)

#1