Formula Change Alert

[C Brandt]

I have a very large spreadsheet where some of the cells are input data and
others are calculated. This sheet is passed around for updates and sometimes
one of the recipients inputs data in cells that normally contain formulas.
This is an intentional event to force data in these cells, therefore the
typical approach of protecting the sheet is not practical.



Is there a way to conditional format the cell to present a background color
change if a cell has been forced and no longer contains the formula?



Any suggestions would be appreciated,



Craig

 

[Earl Kiosterud]

C,

Better yet, use some kind of formatting (a fill color, maybe) to indicate
the cells containing formulas and a label somewhere that tells users not to
mess with them.

Better yet, unlock the cells that are to be changed (Select the cells, do
Format - Cells - Protection). Then protect the sheet (Tools - Protection -
Protect sheet).

If you don't like that solution, use Data Validation. Select the cells with
the formulas, Data - Validation - Custom, and put in "pigs"="fly" with the
quotes. It will never allow anything to be entered into the cell, unless
someone removes the data validation. In the "Input Message" tab, put
something like DO NOT PUT ANYTHING INTO THIS CELL. In the Error Alert tab,
put something like DIDN'T WE TELL YOU NOT TRY TO PUT ANYTHING INTO THIS
CELL, YOU MORON? Say it nicely, of course.

The second solution is the best.

 

[Bernie Deitrick]

Craig,

You could use the workbook's sheetchange event: the code below will color any single cell red when
that cell has a formula overwritten by a value. If you have any conditional formatting, you would
need to remove it from that cell....

Copy the code, and paste it into the codemodule of the Thisworkbook object, and give it a try.

HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim myTemp As Variant
If Target.Cells.Count > 1 Then Exit Sub
If Target.HasFormula Then Exit Sub
With Application
.EnableEvents = False
myTemp = Target.Value
.Undo
If Target.HasFormula Then Target.Interior.ColorIndex = 3
Target.Value = myTemp
.EnableEvents = True
End With
End Sub