Conditional formatting to identify hard-coded cells

[simonalcock]

Hi all,

I have a spreadsheet in which some cells have lost their formulas. The calculated value is still displayed, but it no longer updates with the rest of the sheet (as if F2, F9 had been pressed).

I need to find out which cells contain hard-coded numbers rather than formulas, and have been trying to use conditional formatting to highlight them for me.

I have a solution, but it seems to make the workbook terribly slow. If anyone has a simpler solution to this problem, I'd love to hear your ideas. My solution follows:

Define the following function in VBA:

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

Apply conditional formatting to the required range of cells, using the following formula to determine which cells to format:

=AND(NOT(OR(isformula(A1),ISBLANK(A1))),ISNUMBER(A1))

 

[muggi]

OK got an solution to my own problem:

Add this function in a Module:

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

and add this in cell N4:

=isformula(G4)

it checks G4, if formula is present, and returns TRUE or FALSE in N4

 

[RoryA]

You could also just change Jon's defined name to:
=GET.CELL(48,INDIRECT("RC[-1]",0))

 

[muggi]

Nice nice nice Rory,
thank you very much for the time finding the solution