CODEWITHSUNDEEP
excel
Maki
Maki

Reputation: 637

Identify cell that don't contain "formula"

version: Excel 2010

Is there a way to quickly identify or create a simple marco to identify cells that doesn't have a formula?

--------------------------------
|=A1*1 |   151 | =A3*1 | =A4*1 |
--------------------------------
|=B1*1 | =B2*1 |   100 | =B4*1 | 
--------------------------------

quickly highlight or change font colour of cell with 151, and 100?

Thank you very much.

Upvotes: 2

Views: 19503

Answers (4)

Arthur D. Howland
Arthur D. Howland

Reputation: 4557

You can use conditional formatting to highlight cells that aren't formulas. This is great for formula driven tables that occasionally need manual input. The manual inputs highlight automatically if they are not formulas.

In conditional formatting highlight a cell then (use formula to determine which cell to format): =ISFORMULA(A1)=FALSE

Then format paint cell A1 around to expand the conditional format.

Upvotes: 0

Sebastian B
Sebastian B

Reputation: 451

You can simply use conditional formatting to highlight cells that not contain a formula.

  1. Open Name-Manager (CTRL+F3)

  2. Create a new name, e.g. "noformula", enter 

    =NOT(GET.CELL(48,INDIRECT("RC",FALSE)))

  3. Create a custom formatting rule for the desired area. Use

    =noformula

    as selection rule.

Upvotes: 2

radiocontrolled
radiocontrolled

Reputation: 517

An easy way to do it without VBA is to select the range you want test, press Ctrl+G and then clickSpecial.

Then specify the type of cells you want and click OK and only those types of cells will remain selected. In your case, select Constants to select all cells without formulas.

image

Upvotes: 2

teylyn
teylyn

Reputation: 35915

In 2010 and earlier, you need a custom VBA UDF for that. Along the lines of 

Function IsFormula(cell_ref As Range)
    IsFormula = cell_ref.HasFormula
End Function

Then use it like

=ISFORMULA(A1)

In Excel 2013 and later there is a built-in function ISFORMULA() that does the same thing natively.

Upvotes: 2

Related Questions