Conditional Formatting-No Formulas

[IvanM]

I want users to be able to see where they have over-written a formula with
text or a value (don't care which). I can't find a function that determines
whether a cell contains a formula. Is there such a function? Or is there
some other way to accomplish this?
thanks

 

[Cimjet]

Hi YvanM
If you go to Menu Tools/Option /View/ Window Options and select Formulas
All the formulas on that sheet will show up.
Hope that helps
Cimjet

 

[Tyro]

If you overwrite a formula, it's gone.

Tyro

 

[IvanM]

Thanks, but this is not what I had in mind. The formulas in question result
in values. We want to allow users to over-write a formula's value with a
different value (manually input), as appropriate. I then want any cell where
the formula's value has been over-written to be automatically highlighted
(say with shading) so supervisors can see where manual intervention has been
taken.

 

[IvanM]

Thanks, but not exactly what I had in mind. I provide further details in my
response to cimjet. Thanks again.

 

[T. Valko]

One way...

Create this user defined function:

Open the VB editor: ALT F11
Open the project explorer: CTRL R
Locate your file name in the project explorer
Right click your file name
Select Insert>Module
Copy/paste the code below into the window that opens on the right:

Function IsFormula(cell_ref As Range) As Boolean
If cell_ref.HasFormula Then
IsFormula = True
Else
IsFormula = False
End If
End Function

Close the VB editor and return to Excel: ALT Q

Select a cell that has a formula, let's use cell A1 in this example:

Select cell A1
Goto Format>Conditional Formatting
Formula Is: =IsFormula(A1)=FALSE
Click the Format button
Select the Patterns tab
Select a fill color (maybe a shade of RED)
OK out

If a user deletes/overwrites the formula in cell A1 the cell will turn RED.

 

[Sandy Mann]

Try something like this in Conditional Formatting:

=A5<> <Your Formula>

where <Your Formula> is the formula in the cell.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Gord Dibben]

Copy/paste this function to a module in your workbook.

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

CF>Formula is: =NOT(IsFormula(A1))


Gord Dibben MS Excel MVP

 

[BB Ivan]

Hi Biff:
This works! Thanks. A difficulty with it, however, is that in order to
apply it to a large range of cells I'll need to set up conditional formatting
indivually for each cell in the range (since the formatting must know what
address to plug into cell_ref). Can you think of any way I could substitute
that in so I can copy the conditional formatting to other cells? I don't see
a function that can give me the address of the "current" cell without being
fed reference infomation.

Thanks again!

 

[BB Ivan]

This is a good, simple answer. I didn't consider that I don't really care
whether the cell contains a formula, but rather whether the value it holds
equals the value that the formula calculates. If someone overwrites the
formula with the same value the formula calcs, I don't really need the cell
to be shaded. Thanks

Biff has a cool solution, too, and it would be cool to see if it could also
work.

 

[Sandy Mann]

You're very welcome.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

One way:

Assume you have this formatting already applied to cell A1.
Select cell A1
Goto Edit>Copy
Now, select the cells where you want the formatting applied.
Goto Paste Special>Formats>OK

 

[Gord Dibben]

Select all the cells prior to Format>CF

The Formula is will increment to all cells in the range.

Just make sure Excel doesn't help by adding the $ signs to make cell refs
absolute.


Gord Dibben MS Excel MVP

 

[BB Ivan]

Great, thanks so much.

One way:

Assume you have this formatting already applied to cell A1.
Select cell A1
Goto Edit>Copy
Now, select the cells where you want the formatting applied.
Goto Paste Special>Formats>OK

 

[BB Ivan]

Great, thanks so much.

Select all the cells prior to Format>CF

The Formula is will increment to all cells in the range.

Just make sure Excel doesn't help by adding the $ signs to make cell refs
absolute.


Gord Dibben MS Excel MVP

 

[Meebers]

I find myself constantly using the "Format Painter" (icon that looks like a
paint brush.) to copy formats.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Great, thanks so much.