I have a large database that I need to distribute to users and have them update. I want to be able to visualize changes that they have entered. Is there a way to use the selection change event to, say, change the font to red, or highlight the color of the cell(s) that they changed?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Identify Changes in Cell Contents in Excel
- Thread starter mllex
- Start date
SkipVought
Programmer
Hi,
The Worksheet_SelctionChange event occurs when a new range is selected. This could be a single cell range or a multiple cell range. In either case, merely changing a selection, is no indication of actual change in VALUES, which is what I believe, you desire.
So, maybe it is the Worksheet_Change event that you want to trigger a change in the Target formatting. Keep in mind that the user can select a multiple cell range prior to changing a value and the entire selection will be included in the formatting unless you make provision for this situation.
But, there is another factor to consider. The Worksheet_Change event is triggered even if the actual has not changed. So you will need to store the Target.Value on Worksheet_SelectionChange and compare it to the Target.Value after the change has occured.
Hope this helps.
Skip,
metzgsk@voughtaircraft.com
The Worksheet_SelctionChange event occurs when a new range is selected. This could be a single cell range or a multiple cell range. In either case, merely changing a selection, is no indication of actual change in VALUES, which is what I believe, you desire.
So, maybe it is the Worksheet_Change event that you want to trigger a change in the Target formatting. Keep in mind that the user can select a multiple cell range prior to changing a value and the entire selection will be included in the formatting unless you make provision for this situation.
Code:
With Target
If .Rows.Count * .Columns.Count > 1 Then
vValue = .Value(1, 1)
Else
vValue = .Value
End If
End WithHope this helps.
Skip,
metzgsk@voughtaircraft.com
- Thread starter
- #3
DaleWatson123321
Programmer
BKDiva,
Here's another solution to tracking the changes...
As Skip (the Master himself) once said, there are ALWAYS many ways to "skin a cat". It's a matter of picking the "sharpest knife".
Here's the relatively simple process:
1) Open your "original" copy of the Excel database.
2) Add a sheet before your "original" data sheet, and name it, for example, "Received".
3) Name your original data sheet "Original" (to match with the formula below).
4) Add another sheet AFTER your "Original" sheet. Name is "Changes".
5) Copy an EXACT "replica" of your "Original" sheet to your "Changes" sheet. In case you are not aware of the method... you click on the "square" above the "1" (for row 1) and to the left of "A" (for column A). Now copy with <Control> C, then click on your "Changes" sheet, and the click on the same "square", and paste with <Control> V.
The importance of coping a "replica", is that the proper "column widths" will also get copied, along with your field names. You might decide to leave your field names for reference.
6) On the "Changes" sheet, enter the following formula in cell A2 (below your field names), and copy it to cover all the cells that your original data covers. Perhaps a larger area to cover for any additional data which might have been added "outside" of the original data range.
=EXACT(CONCATENATE(Received!A2,"",CONCATENATE(Original!A2,"")
BEFORE copying the formula, however, FIRST use "Conditional Formatting" to enable "Highlighting" of any differences or changes between your "Original and the data "Received".
Steps for Conditional Formatting:
1) From the menu... Format - Conditional Formatting, then enter: cell value is equal to FALSE.
2) Choose "Format", then the "Patterns" tab, and choose a bright COLOR. Then "OK" twice.
Final Note: This formula will highlight all changes where the change is to TEXT or a VALUE, but NOT where there might have been change(s) to FORMULAS.
IF there are formulas involved, and you want to ensure that NO changes were made to FORMULAS, then the following can be done to (temporarily) convert the formulas to TEXT.
1) On both the "Received" sheet and your "Original" sheet, use <Control> A to select the entire sheet.
2) Use "Replace" <Control> H.
3) Under "Find what:", use "=", and
4) Under "Replace with:", use "|"
5) Use <Alt> A - to Replace All.
Use of a unique character such as the "|" will allow you to easily "reverse" the "TEXT" formulas back to "normal" formulas, using the same "Replace" process in reverse.
Hope this gives you an alternative.
Please advise as to whether this is a useful option, and indeed which option you chose.
If you (or ANYONE) would like an actual copy of this file, just email me and I'll send a copy via return email.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
Here's another solution to tracking the changes...
As Skip (the Master himself) once said, there are ALWAYS many ways to "skin a cat". It's a matter of picking the "sharpest knife".
Here's the relatively simple process:
1) Open your "original" copy of the Excel database.
2) Add a sheet before your "original" data sheet, and name it, for example, "Received".
3) Name your original data sheet "Original" (to match with the formula below).
4) Add another sheet AFTER your "Original" sheet. Name is "Changes".
5) Copy an EXACT "replica" of your "Original" sheet to your "Changes" sheet. In case you are not aware of the method... you click on the "square" above the "1" (for row 1) and to the left of "A" (for column A). Now copy with <Control> C, then click on your "Changes" sheet, and the click on the same "square", and paste with <Control> V.
The importance of coping a "replica", is that the proper "column widths" will also get copied, along with your field names. You might decide to leave your field names for reference.
6) On the "Changes" sheet, enter the following formula in cell A2 (below your field names), and copy it to cover all the cells that your original data covers. Perhaps a larger area to cover for any additional data which might have been added "outside" of the original data range.
=EXACT(CONCATENATE(Received!A2,""
,CONCATENATE(Original!A2,"")BEFORE copying the formula, however, FIRST use "Conditional Formatting" to enable "Highlighting" of any differences or changes between your "Original and the data "Received".
Steps for Conditional Formatting:
1) From the menu... Format - Conditional Formatting, then enter: cell value is equal to FALSE.
2) Choose "Format", then the "Patterns" tab, and choose a bright COLOR. Then "OK" twice.
Final Note: This formula will highlight all changes where the change is to TEXT or a VALUE, but NOT where there might have been change(s) to FORMULAS.
IF there are formulas involved, and you want to ensure that NO changes were made to FORMULAS, then the following can be done to (temporarily) convert the formulas to TEXT.
1) On both the "Received" sheet and your "Original" sheet, use <Control> A to select the entire sheet.
2) Use "Replace" <Control> H.
3) Under "Find what:", use "=", and
4) Under "Replace with:", use "|"
5) Use <Alt> A - to Replace All.
Use of a unique character such as the "|" will allow you to easily "reverse" the "TEXT" formulas back to "normal" formulas, using the same "Replace" process in reverse.
Hope this gives you an alternative.
Please advise as to whether this is a useful option, and indeed which option you chose.
If you (or ANYONE) would like an actual copy of this file, just email me and I'll send a copy via return email.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
DaleWatson123321
Programmer
BKDiva,
We're all waiting...
And... "drum roll" please... the WINNER is...
...Sure would be nice to know what "course of action" you took.
Or, for that matter, whether you still require help.
Please advise.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
We're all waiting...
And... "drum roll" please... the WINNER is...
...Sure would be nice to know what "course of action" you took.
Or, for that matter, whether you still require help.
Please advise.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
- Status
Similar threads
- Locked
- Question
- Locked
- Question
