Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
How do I add (auto text) to a paragraph? | Excel Worksheet Functions | |||
How do I change macro text with another macro? | Excel Discussion (Misc queries) | |||
HOW CAN I AUTO FILL A CELL WITH TEXT FROM ANOTHER WORKSHEET TEXT . | Excel Worksheet Functions | |||
change typed text to upper case | Excel Discussion (Misc queries) |