Formula or Value

[gheyman]

Is there a way to do conditions based on if the cell has a formula in it or if its a value?

I want to be able to identify if someone overwrote a value in a cell with a hard value.

I am going to either use this in some VBA code (If statement) or just use conditional formating.

Thanks for the help!

 

[p45cal]

I doubt it with a built-in formula, but if you're going to use vba you can have a udf:

Function hasformula(cll)
hasformula = cll.hasformula
End Function

and use it on the sheet thus:

Excel Workbook
	D	E
18	23	FALSE
19	23	TRUE
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D18	1. / Formula is =hasFormula(D18)	Abc
D19	1. / Formula is =hasFormula(D19)	Abc

Excel Jeanie HTML isn't working for me tonight; D19 was highlighted on my sheet by conditional formatting - honest.

 

[gheyman]

Not sure how to follow. But I am going to do some research based on what you sent me.

Thanks for the start.

 

[Peter_SSs]

One of the issues with using Conditional Formatting is to remember that if the user pastes something in a conditionbally formatted cell the CF is over-written.

Another way that may suit you is to simply select the column/range/cells where you expect to find the formulas then press F5 -> Special...|Constants|OK. If any formulas have been over-written with values those cells will be highlighted.

The above would not pick up if formulas have simply been deleted. However you could use the same process but select 'Formulas' instead of 'Constants' and look for any cells that become de-selected. Blanks and constants would be de-selected with this process.