Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
rrucksdashel
 
Posts: n/a
Default 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   Report Post  
Bernd Plumhoff
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formulas dealing with text data Bagia Excel Worksheet Functions 6 June 20th 05 10:29 PM
How do I add (auto text) to a paragraph? Lisa Nelson Excel Worksheet Functions 1 May 4th 05 04:07 PM
How do I change macro text with another macro? Eric Excel Discussion (Misc queries) 4 April 27th 05 11:20 PM
HOW CAN I AUTO FILL A CELL WITH TEXT FROM ANOTHER WORKSHEET TEXT . waz Excel Worksheet Functions 2 December 29th 04 08:49 PM
change typed text to upper case CT Cameron Excel Discussion (Misc queries) 2 November 30th 04 02:07 AM


All times are GMT +1. The time now is 02:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"