Can I auto change text to RED if a user overwrites a formula?

R

rrucksdashel

I have a template that many users access. I have some cells that contain
formulae that they sometimes want to overwrite. Can I set the text in that
cell to show up as red if they overwrite the formula with hard input?
 
B

Bernd Plumhoff

Hi,

I suggest:
Define the name HasFormula
=GET.CELL(48,INDIRECT("RC",FALSE))
(Go to Insert/Name/Define, enter as name HasFormula and in field "Refers
to:" =GET.CELL(48,INDIRECT("RC",FALSE))
[don't forget the leading =])
Then select all cells of your relevant sheet (click at upper left corner).
Go to Format/Conditional Formatting.., select Formula is and enter in
adjacent field
=NOT(HasFormula)
and select a desirable format (Font, Color, Red, for example)
Now all cells with a formula in them appear in normal black font while
overwriten constant values will be shown in red.

Apply the conditional format only for some cells if you do not want all
cells to be affected.

Other interesting GET.CELL()-arguments you might find at
http://www.sulprobil.com/html/get_cell.html

HTH,
Bernd
 
G

Gord Dibben

Add this Function to a module in your workbook.

Function IsFormula(Cell)
Application.Volatile
IsFormula = Cell.HasFormula
End Function

Then select the cells in question and Format>Conditional Formatting>Formula
is:

=ISFORMULA(cellref)=FALSE

Select a red font from the Format>Fonts and OK your way out.

Alternative............protect those formula cells so's the users cannot
overwrite them.


Gord Dibben Excel MVP
 
Top