Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you change the color of one cell based on the color of another | Excel Discussion (Misc queries) | |||
how can I conditionally change font color, or background color? | Excel Worksheet Functions | |||
How to change the default Border, Font Color, and Cell Color | Excel Discussion (Misc queries) | |||
Change of text or background color doesn't change on the screen. | Excel Discussion (Misc queries) | |||
Allow users to change the color of the comment indicator color so. | Excel Discussion (Misc queries) |