Ex07 in '03 Format - Change format if information changes

[TraciAnn]

I need code to either change the formatting or to set a value in a cell if
data in that row has been changed. It may work best to set a value in a
column that is locked when the worksheet is protected and then is
reset/removed after the updates have been identified.

Here are the details if it helps:
I distribute a workbook that is maintained by outside sources. The workbook
contains two tabs/sheets, Location and Technician.

The Location worksheet contains mostly information which is maintained by me
and is protected/locked when the workbook is distributed. This worksheet also
contains columns that are validated data using a list identified by a dynamic
named range on the second worksheet. These columns are not locked when the
worksheet is protected. When a change occurs on this sheet I would like for
it to be visually obvious, either by a value in a new column, or by the
formatting of the changed text (e.g. bold text).

The Technician worksheet is maintained by the outside sources. It contains
names and addresses of resources used and assigned to the Locations on the
first worksheet. When a change occurs on this sheet I would like for it to be
visually obvious, either by a value in a new column, or by the formatting of
the changed text (e.g. bold text).

Only one column is locked on the Technician worksheet. It is a formula that
creates a unique ID based on the input of some of the information in the
other columns.

On both sheets, I use conditional formatting to provide alternating
background colors to make the information more ledgible:
=AND(MOD(SUBTOTAL(3,$A2:$A$2),2)=0,$A2<>"")

Location also has Conditional formatting in eight columns which applies Gray
background to columns that should not be populated. This condition is based
on a value entered entered in another column (1 through 8).

 

[OssieMac]

Hi TraciAnn,

See if the following will help.

Assume that the worksheet in question is called "My Sheet"
Insert a new worksheet to store all the values. Call it "Master Copy"
Select all the cells in "My Sheet" and then Copy.
Change worksheets to "Master Copy"
Select Cell A1 and then Paste Special -> Values.
Protect and hide worksheet "Master Copy".

You can then use the following code to identify the cells that have been
changed.

Sub TestForChanges()

Dim wsMysheet As Worksheet
Dim wsMastCopy As Worksheet
Dim c As Range

Set wsMysheet = Sheets("My Sheet")
Set wsMastCopy = Sheets("Master Copy")

With wsMysheet
For Each c In .UsedRange
If c.Value <> wsMastCopy.Range(c.Address) Then
c.Font.Bold = True 'See Other options below
End If
Next c
End With

End Sub

'Other options to highlight changes

'Color the background
c.Interior.ColorIndex = 3 'Red

'Color the font
c.Font.Color = vbRed

 

[TraciAnn]

Ossie,

Yes, this works. However, I didn't think this through completely. Your code
works but I don't know how to keep the data synchronized between changes.

I'm going to reword my questions in another post with a more descriptive
subject.

Please look for it and tell me if you can expand on your answer any.

Thanks so much for your help!!!