Excel - detect a changed value in cell 2

[JustLJ]

Greetings.

I send out tons of spreadsheets to remote office/workers for them to update certain cells (if needed) and return to me. I'm looking for a way to detect that the value has changed from the time I sent it until it's back to me.

For example, cell D30 was sent out as 50 and they send it back to me as 90. I want to be able to tell the value has changed (because there are really 6 columns * 150-to-350 rows * about 40 sheets every day).

I've tried conditional formatting, but apparently not able to determine the correct formula. I can't find a "on change" event like in Access to use...

The cell values can be blank or 0 thru 100 by 10's, if that strikes a thought for anyone.

Your thoughts greatly appreciated!

LJ

 

[PaulTEG]

LJ,

You could try, and I stress try, sending out a hidden copy of the sheet in the workbook, and if a difference between the the two cells (on the visible and hidden) exists, then highlight using conditional formatting.

Personally, I'd use the spreadsheet and run it against the original to generate a text version of (only) the diffs

Hope that's of some help

--Paul



It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...

 

[byundt]

Have you considered using the Track Changes feature? You can find it on the Tools...Track Changes menu item (Excel 97 and later). When the workbook is given back to you, all the changed cells will have a black triangle as an alert that a change was made.

Note that using Track Changes shares the workbook and prevents you from looking at VBA code (though macros and user defined functions still work).

If you want to play with VBA, you can even mark the changed cells with highlighting or font color changes. Here is a link to some code I wrote that makes the font in changed cells red:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=10

 

[JustLJ]

byundt:
Thank you, thank you, thank you! I have to really do a "DUH" on me, as I use the track changes feature in Word all the time when I send things out and it just didn't occur to me that Excel would have the same thing.
Star for you indeed.

And Paul I agree with what you posted, I had thought the same thing and if it were just a single one, I'd try that, but the shear volume would kill me! But a star because it is a workable solution and may help some future reader.

As usual, TekTips is the best!
LJ

 

[SkipVought]

LJ,

I know this is the MS Office Forum and NOT the VBA forum, but you mentioned the OnChange event in Access.

You might want to check out the WorkSheet_Change event. Right click the sheet tab and select View Code. Above the Code Window in LH corner, drop down and select Worksheet, in RH corner, drop down select Change.

Post in the VBA forum if you need any further assistance.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244