Color change

Bill Manville;537121 Wrote:
>
The access database tracks the number of patients for an outbreak. The
data are updated at least weekly (as we obtain new information) until
the outbreak is closed out. In my access query, it will not export data
to excel if the “status” variable is “closed”; the query will only
export data with an “open” status. Also included are text comments,
which are updated as well. There are two types of updates -- updates to
an existing record and updates when a new record is added (a new
outbreak).

Is there a way to go about this with some sort of a time/date stamp
instead? I am only pulling data from excel once a week. Is there is a
way for excel or access to recognize changes after a certain date? When
I export data seven days later, excel will note the changes and make the
new data red and bold.

Thanks!




--
sunflowergarden

>Is there a way to go about this with some sort of a time/date stamp

Only if the Access records have a date/time of last update field which
requires code in the relevant form to set it when the record is
updated.

If there is such a field and you import it along with the rest then you
could certainly highlight changed fields - either by using a macro
after refreshing the data or by the use of conditional formatting
together with a cell containing the date/time of the previous query.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

>
Thanks for the suggestion but I'm really a novice at programming so I
don’t think it’ll be something that I could tackle…

I have another idea based on what you suggested earlier – it needs some
tweaking though because it’s not working. I decided to save the old data
from the previous week in one sheet (week 1) within the workbook and
compare it with a second sheet (week 2) that will have the new data. I
would like to use conditional formatting to do that, however, I started
running into problems when my range was extended:

Formula is=Data1<>$B$2:$AF$4 (then make red and bold)

(Data1 refers to the same range from week 1)

I appreciate any suggestions!

Thanks again!!




--
sunflowergarden

As I said at the start, what you are trying to do is very difficult if
the set of records changes from one run to the next (with deletions and
additions).

If your records have a unique key in column A and your previous query
results are in sheet Week2 then you could use a conditional format
based on the formula (created when the active cell is in row 2)
=ISNA(MATCH($A2,Week2!$A:$A,0))
that will highlight rows that are new.

A second conditional format could be used to detect fields that have
changed in records that are common to new and old queries:
=A2<>VLOOKUP($A2,Week2!$A:$AF,COLUMN(),FALSE)

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup