Conditional Format-possible to show overridden formulas?

  • Thread starter Thread starter GretOgrady
  • Start date Start date
G

GretOgrady

I've got a cell that has a formula in it and I'd like to set up a
conditional format so that it turns a different color if someone
instead overrides the formula and inputs a value - is this possible?
Thanks
 
You could have a UDF that checks if it is a formula, and use that in the CF


Function IsFormula(rng As Range) As Boolean
IsFormula = rng.HasFormula
End Function

and use like =IsFormula(A1)


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
You could use a user defined function that returns a true/false result if the
cell has a formula.

Option Explicit
Function CellHasFormula(rng As Range) As Boolean
CellHasFormula = rng(1).HasFormula
End Function

Then use that in the format|conditional formatting formula:

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But I have a followup suggestion.

If you lock those cells with formulas (format|cells|protection tab), then
protect the worksheet (tools|protection|protect sheet), then you don't have to
worry about the user writing over the formula.

(Unlock the cells that that should allow user input, though.)

And a suggestion if you have to allow either the formula or the typing...

Use a couple of helper cells.

A1 can hold the formula
B1 can hold the superseding value
C1 can hold a formula to determine which to use:
=if(b1="",a1,b1)

Then use C1 in all your other calculations (and still lock all those formula
cells.)
 
Thanks so much for your quick and thorough response - I've worked with
macros but hadn't yet worked with UDFs so your reference to David's
explanation was also very helpful!!
 
Back
Top