Conditional Formatting for cells that have a formula, then are overwritten manually. 2

[AELLC]

Is it possible to set it up so the cell color changes when I manually type a number into a cell that has a formula, i.e., deliberately replacing the formula with the desired number?

I like to provide a sort of "manual override" in my design workbooks, and wanted to see at a glance which cells had been overridden. Frequently the overridden cell has cell dependents, and that helps the way my workbooks function...but there are cases where is overlooked that I should restore to the original formula.

If this is not possible, then I will continue to use Formula Auditing, Show Formulas, but it would be great to do as described above.

Another way would be to set up the adjacent cell with the same formula but no dependents, to show me if the 2 cells display different values, but that is cumbersome in my congested workbooks.

 

[MintJulep]

Yes, I think it can be done.

Write a UDF that returns TRUE If AND(Range.Formula is Nothing, NOT(Range.Vaue is Nothing))

Use that UDF as the formula in Conditional format by Formula.

 

[AELLC]

Thanks, but I am totally UDF, macro, and VBA - challenged.

Even though I work on my Excel programs to improve for significant periods of time most days, for some reason all that baffles me.

I was very sharp at picking up on Fortran IV (instructor said I was best in class) back at University in the 70's, and programing HP calculators subsequently, but I am just not comprehending those Excel methods.

 

[MintJulep]

Well, seems like the opportunity to learn has arrived!

I'd offer a functional solution - but I don't have anything with Excel installed available at the moment.

 

[CarlB]

Your conditional format rule would be:
=ISFORMULA(B2) = FALSE

where "B2" is particular cell conditional format is applied.
Set "fill" to some color.

 

[MintJulep]

Duh!

Nice one Carl - I totally forgot that you can access formularity from a native function.

 

[AELLC]

Carl,

I can't get it to work.

See cell G8 on sheets GT and GT(2)

 

[CarlB]

Cell G8 on tab G2 had 2 different formatting rules-
one for true, one for false. I deleted the one for true (green). It seemed to work then, but not at first go. Also it should have worked even with both rules, using order for preference.

 

[AELLC]

I deleted both true rules (green)

Still don't work.

grrrr.

 

[CarlB]

Well on the G(2) tab there are some format issues.

Instead of
="IF(ISFORMULA(G8)) = TRUE"

use

=ISFORMULA(G8) = TRUE

Try deleting then re-entering the conditional formatting rule if it's not working.

 

[AELLC]

Still doesn't work.

If your version works, PLEASE send it.

 

[AELLC]

If all else fails, I can make a rule where it tests to see if G8=H8. By adding a step before H8 to round it to an integer value, because normally it had the 26 decimal places by calculation, that DOES seem to work.

 

[CarlB]

Try the attached. It didn't take at first for me either, I had to go in to "edit rule" than save, then it took effect. Quirky.

 

[AELLC]

Thanks for your time but even your version doesn't work here.

I am going to Plan B, my 21:51 post. Almost as good.

 

[AELLC]

Now that have looked into this some more, =ISFORMULA(B2) = FALSE will never work because I can't find any function that is called ISFORMULA. When I try that in a cell as a formula, Excel returns #NAME?

However, when creating new rule in conditional formatting, Excel does not return any error flag, just nothing happens.

 

[MintJulep]

So back to my original suggestion then.

Public Function IsOverridden(Target As Range) As Boolean
IsOverridden = Left(Target.Formula, 1) <> "="
End Function

 

[CarlB]

ISFORMULA is apparently new in 2013

http://blog.contextures.com/archives/2013/02/05/new-isformula-function-in-excel-2013/

So, the UDF is the way to go for you.

 

[AELLC]

Carl, then that explains that. I have 2010.

 

[IRstuff]

You could, however, make an adjacent cell change color, assuming that they delete the equal sign or some part of the formula, with a value,say,

if(left(a1)="=sin(",true,false) Then, this cell could be conditionally formatted to change color

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

 

[IRstuff]

oops

if(left(a1)="=sin(",5),true,false)

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529