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

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

(OP)

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.

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.

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

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.

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

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.

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

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

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

=ISFORMULA(B2) = FALSE

where "B2" is particular cell conditional format is applied.

Set "fill" to some color.

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

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

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

I can't get it to work.

See cell G8 on sheets GT and GT(2)

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

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.

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

Still don't work.

grrrr.

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

Instead of

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

use

=ISFORMULA(G8) = TRUE

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

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

If your version works, PLEASE send it.

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

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

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

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

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

## CODE --> VBA

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

http://blog.contextures.com/archives/2013/02/05/ne...

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

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

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

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

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

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

Wow, talk about not seeing the obvious.

I think my problem is I overthink everything.

## RE: Conditional Formatting for cells that have a formula, then are overwritten manually.

FAQ731-376: Eng-Tips.com Forum Policies