#1   Report Post  
Junior Member
 
Posts: 5
Default Color change

Hi,

I'm new to this forum and have been checking for solutions, no luck so far so hope someone can help me out.

I have an access database and I am able to export successfully to excel using "import external data". When I enter new data into access, it will refresh in excel. What I have been trying to do is write a code (newbie to VB) so that when data are updated/entered in access, the new data entered will change to red and bold when it is refreshed in excel. I appreciate any suggestions.

Thanks!!!
  #2   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Color change

In general, that's quite a difficult thing to do (unless the changes
are only ever new records being added to the end of the data).

You would need to have a copy of the previous data, with which to
compare the new data. The new data might not be in the same sequence
as the previous data, rows might have been deleted etc.

If you can give a bit more information about the data, and the types of
change that will occur then we might be able to help further


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

  #3   Report Post  
Junior Member
 
Posts: 5
Default

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!
  #4   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Color change



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

  #5   Report Post  
Junior Member
 
Posts: 5
Default

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!!


  #6   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default Color change

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

  #7   Report Post  
Junior Member
 
Posts: 5
Default

Your second condition worked perfectly! Thank you very much. I have some minor tweaking to do but you got the hardest part to work for me!
  #8   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 7
Default Color change

Try a changes made post a certain date function in your code and formatting.
"sunflowergarden" wrote in message
...

Hi,

I'm new to this forum and have been checking for solutions, no luck so
far so hope someone can help me out.

I have an access database and I am able to export successfully to excel
using "import external data". When I enter new data into access, it will
refresh in excel. What I have been trying to do is write a code (newbie
to VB) so that when data are updated/entered in access, the new data
entered will change to red and bold when it is refreshed in excel. I
appreciate any suggestions.

Thanks!!!




--
sunflowergarden


  #9   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 7
Default Color change

Get the access databaseto capture date and time of entry. Then on excel
format all changes beyond a date to show color of your choice. This should
not be too difficult.
"Bill Manville" wrote in message
...
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can you change the color of one cell based on the color of another andoscott Excel Discussion (Misc queries) 4 May 4th 07 04:02 PM
how can I conditionally change font color, or background color? MOHA Excel Worksheet Functions 3 August 21st 06 06:57 PM
How to change the default Border, Font Color, and Cell Color Elijah Excel Discussion (Misc queries) 3 November 3rd 05 12:52 AM
Change of text or background color doesn't change on the screen. Susan Excel Discussion (Misc queries) 5 July 29th 05 07:18 PM
Allow users to change the color of the comment indicator color so. DanC Excel Discussion (Misc queries) 1 February 25th 05 06:15 PM


All times are GMT +1. The time now is 03:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"