Conditional Formatting When Cell Changed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I change the format of a cell (either color, bold, or fill) if the
cell is modified? I need managers to update a worksheet but I want to know
what cells they have modified from the original worksheet sent.
Many thanks.
 
Careful with your terminology, because Conditional Formatting applies a
format based on the current cell value regardless of how that value came\
about. http://www.mvps.org/dmcritchie/excel/condfmt.htm

What you are looking for in the manner you asked for can be achieved by
including some Event macros, needless to say replacement by the original
value would be marked, and you would have to have a means perhaps
something in another worksheet that the event macros can check so that
they will only change the formatting when allowed to do so.
http://www.mvps.org/dmcritchie/excel/event.htm

Excel has a builtin feature for this purpose, not exactly like what you
asked for called "Highlight Changes" which will mark cells and keep a
log of who made changes and when, which is probably closer to your intent.
Highlight, Change Highlighting
http://www.mvps.org/dmcritchie/excel/highlite.htm
Probably useful for what your purpose is -- during proofing, but a
huge annoyance for anything undergoing anything but very minor changes
in a short period of time. Be sure you know how to turn it off (see my
page
above) so you won't have to use a backup file to get things back to normal.
 
Conditional Formatting won't do this.

You could do this with an event macro, but you might want to look at
"View the history of changes to a workbook" in XL Help as an alternative.

The event macro could be something like this, placed in the ThisWorkbook
code module of the workbook:

Private Sub Workbook_SheetChange( _
ByVal Sh As Object, _
ByVal Target As Excel.Range)
Target.Interior.ColorIndex = 3
End Sub

Note that this doesn't provide a way to Undo in the event of a mistake.
 
Back
Top