***Cell Shading (Fill Color)***

[Steve]

Hi all,

I need some help. I'm looking for a way to do this. I have columns with
formulas. And sometimes those formulas in a particular cell or 2 are
overwritten with a "hard" number. Is it possible to somehow make it so that
when someone changes the cell to a number that it turns the cell yellow
automatically?

Please help.

Thank you!!

 

[JP]

Why not just train people not to do that?

You could write some complicated VBA code that checks the content of a
cell to see if it contains hard coded values. In the Worksheet_Change
Event, it could act as a realtime monitor. There are also some
spreadsheet auditing programs that can do this for you. Check out:
http://arxiv.org/ftp/arxiv/papers/0803/0803.0169.pdf



HTH,
JP

 

[Rick Rothstein \(MVP - VB\)]

You can let your users type away and still prevent them from changing the
cells a specified range. For example...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
On Error GoTo Done
Application.EnableEvents = False
Application.Undo
End If
Done:
Application.EnableEvents = True
End Sub

will prevent the user from making a change to anything (formulas or
constants) in Column D. Simply change the If-Then test to cover whatever
range you need it to cover.

Rick

 

[Peter T]

I would have thought the most obvious thing to do, if user should not change
the cell at all, is to protect the sheet with those cells locked. However if
user is allowed to amend formulas you could use Conditional formats.

First select cell A1 ( * important * )
Ctrl-F3, Define name
Name: NotFormula
Refersto: =LEFT(GET.CELL(41,A1),1)<>"="

Select your Formula cells you want coloured if they are changed to a non
formula, ie do not start with an "="

Format, Conditional formats, select "Formula Is" in the left dropdown and
this formula
=NotFormula

(I'm sure there must be some simpler function to indicate if the cell is a
formula, but off the top of my head I can't think what it might be).

Regards,
Peter T