INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

## 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.

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

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.

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

(OP)
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.

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

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.

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

Your conditional format rule would be:
=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.

Duh!

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.

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.

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

(OP)
I deleted both true rules (green)

Still don't work.

grrrr.

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

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

="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.

(OP)
Still doesn't work.

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

(OP)
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.

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

(OP)

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.

(OP)
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.

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

So back to my original suggestion then.

#### CODE --> VBA

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

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

(OP)
Carl, then that explains that. I have 2010.

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

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: 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.

(OP)
IR,

Wow, talk about not seeing the obvious.

I think my problem is I overthink everything.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!