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

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?

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

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 FormatConditional FormattingFormula
is:

=ISFORMULA(cellref)=FALSE

Select a red font from the FormatFonts and OK your way out.

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


Gord Dibben Excel MVP

On Mon, 12 Sep 2005 12:55:03 -0700, rrucksdashel
wrote:

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?