Excel 2010 Conditional Format Cells that are a Value, not a Formula 2

[sjp0362]

I would like to add a conditional format to cells that currently have formulas, so that if a person enters a value over the formula, it would be easy to detect which parts of a worksheet would no longer be calculating.

Is this a possibility?

I'm using Excel 2010, and loving it.

 

[SkipVought]

Unlock the cells that users can enter data and then PROTECT the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sjp0362]

Sorry, I should have added that I don't want to protect the sheet. Reasoning is that ocassionally we want to overwrite the formula for some special reason. Weeks or months later, someone opens the worksheet and revises a couple of numbers, but they forgot they overwrote one formula so now things aren't calculating correctly.

When might this happen? We're competitively bidding a project and add a certain percentage to the estimated costs for our markup to get to our TOTAL BID. When we need to give our bid breakdown, we want our fee spread proportionately throughout the breakdown, but we also want all numbers rounded to nearest dollar. If numerous numbers are rounded down, the breakdown doesn't match the TOTAL BID. Therefore prior to distributing the breakdown, we change one or two numbers manually. I want to be able to easily see what numbers I manually entered over the formula.

So, I was wondering if someone might know of a way to write a conditional format, that if the cell didn't contain a formula, I would get certain formatting.

 

[Deniall]

Create a UDF along the lines of

Function IsFormula(A)
IsFormula = A.HasFormula
End Function

Then use this in the formula part of the definition of your conditional format.

 

[sjp0362]

Went into Developer, View Code, and created the code you noted. Now how do I use this is my conditional format?

I'm assuming it would be Conditional Format "Use a formula to determine which cells to format", but apparently I'm not writing my formula correctly, as I'm not getting anywhere.

 

[SkipVought]

Overwriting formulas is NOT a good practice. In fact it is a BAD practice!

If you need an override value, build it into a formula!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Deniall]

With XL-2010, go into
Developer > VisualBasic > Insert > Module
and paste the code in there.

Get back to the spreadsheet. Then if the cell that you wish to subject to this process is B3:
» Select B3
» Conditional Formatting
» "Use formula to determine ..."
» In the formula box enter =not(IsFormula(b3))
» Finally, define the format you want to be applied when the cell B3 does not contain a formula.

 

[GlennUK]

Look at this method ( and please note Skip's comments ):

http://j-walk.com/ss/excel/usertips/tip045.htm

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[lionelhill]

You can also use Ctrl-` to toggle to formula view, in which it's usually pretty obvious when a cell has been changed to a value (most formulae being vastly longer than most values)

 

[sjp0362]

Thanks Deniall for your help. This is exactly what I was looking for.

I agree with other comments, that you don't want to overwrite any formulas.

 

[sjp0362]

I agree with comments that you don't want to overwrite formulas, but there are instances where that's exactly what my various bosses want done. And the formulas being overwritten vary and there is no consistency in how they want them overwritten.

 

[SkipVought]

that's exactly what my various bosses want done

So you mean that the formula will no longer be used on the sheet?

Or is it that the boss wants to immediately enter an OVERRIDE value, but want the formula for 'next time'?

You can help youself AND YOUR BOSS out by accomodating his/her desire to OVERRIDE the formual, by construcing an appriate override formula like this...
[tt]
=If(Override="",OriginalFormula,Override)
[/tt]
Of course you will want some way to CLEAR any and all override values, but THAT is the way to preserve and PROTECT all formulas!!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sjp0362]

SkipVought, you've sparked by interest with the Override.
Where do I enter the Override values--do I create a cell for the Override's?

 

[SkipVought]

Yes!

Suppose that A1 were the cell containing the formula as I proposed above and A2 were the cell reserved for an override value.

Using a very simple example, lets assume that your original formula in A1 were =[red]B1*(C1+7)[/red].

The NEW formula in A1...
[tt]
=IF(A2="",[red]B1*(C1+7)[/red],A2)
[/tt]

When there is NO VALUE in A2, A1 displays the values from the original formula. Otherwise, A1 displays the override value in A2.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sjp0362]

I've done numerous formulas like that in the past, don't know why I didn't think of using it in this particular case.

However, I'm still thinking about using Deniall's suggestion on some of my more complicated templates that I create for everyone to use. Even though I've added text into the Data Validation area noting that field is a calculated field, I've had co-workers enter a value over one of my formulas. If I setup the conditional formatting thing for these cells that have formulas, when a person enters a value, having the cell turn a bright color will definitely catch their attention and they would immediately know that they did something wrong.

 

[SkipVought]

A cell that is formatted with Data Validation does not have any of the supporting formula exposed.

And if someone PASTES a value over a DV cell, NOTHING REMAINS of the former DV!

In fact, if someone overwrites ANY FORMULA, your proposed CF shades go away!!!

What you need is to create the entire form in VBA. If it were me, under these circumstances, I would put NO FORMULAS on the sheet, but do all the control logic, using Sheet Event processing.

If you wish to pursue such a solution, start a new thread in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

You can use generic excel function instead UDF, the formula in CF A1:
=(CELL("type",A1)="v")

combo

 

[combo]

...oops, sorry. The formula will return 'v' for numeric values too.

combo

 

[lionelhill]

It's also really annoying that Excel's inconsistent-formula warning will put a mark in the corner of a cell which contains a different formula to its neighbours, but won't recognise a cell that is a number while neighbours are a formula. I'd guess the former is less frequently done, and the latter is certainly hard to spot.