How to code excel to have cell change color when data is edited

**joyfulnoise55** · 05-12-2016, 11:01 AM

I am about to start a database audit where I will give each of our staff members excel exports of our database data. They will then go through the spreadsheets and make any necessary changes to the data, and I will then upload the changes back into the database. What I need is to be able to easily identify and isolate the cells that our staff changes so that I can upload the changes. While excel "track changes" is great, it doesn't appear to be sortable/filterable so that I could easily identify and isolate the changes for upload. I was hoping for a way to have the cell actually change color, or at least have the text of the cell change color, when a change was made so that I could sort based on color. I found this older thread which appeared very hopeful: http://www.excelforum.com/l/738980-asdf.html

I'm excited to try this posted solution but when I right-click the sheet tab, and click viewcode the window that pops up doesn't seem to have a place I can copy the code. I'm pretty unsophisticated when it comes to excel, but know enough to get myself in trouble. I'm using Office 2013. Not sure if that makes a difference. Any help would be appreciated. (Hopefully) attached is the screenshot of the "viewcode" screen I get.

Also, if anyone knows of a better solution to my overall goal of tracking and identifying changes (meaning I could filter them out) in a huge amount of data I'm all ears. Thank you so much!

Excel Viewcode.JPG

**Arkadi** · 05-12-2016, 11:06 AM

Hi joyfulnoise55,

To paste a worksheet change event, bring up the vba editor (alt-F11), and then on the left near the top you should have a VBA Project window. If VBAProject is not expanded, click the +, "microsoft excel objects" should be listed, and if that is expanded, then you see the sheets as well as "ThisWorkbook". Double click the sheet that you want the code to work for, and paste your code.

**joyfulnoise55** · 05-12-2016, 12:57 PM

Thank you for the quick response! Unfortunately, when I click the "VBAProject (Audit Projects.xlsx)" project in the VBAProject window I just get an error message that says "Project is unviewable".

Could it have anything to do with the fact that my workbook is saved on our company server? I wouldn't think so, but I guess you never know!

**Arkadi** · 05-12-2016, 01:14 PM

It shouldn't be... Is it a shared workbook? that locks the code area as far as I know.... if it is shared, unshare it temporarily?
if it isn't shared, you may try sharing, then un-sharing the workbook to see if that unlocks it.

**joyfulnoise55** · 05-12-2016, 01:21 PM

Oh how exciting! My workbook WAS shared because I had turned on the track changes feature earlier when I was playing with it. I unshared it and added that code, and it works like a charm

THANK YOU for your help!!

**Arkadi** · 05-12-2016, 01:42 PM

Perfect

If this solved the issue then please mark the thread as closed when you get a chance?

How to code excel to have cell change color when data is edited

LinkBack

Thread Tools

Rate This Thread

Display

How to code excel to have cell change color when data is edited

Re: How to code excel to have cell change color when data is edited

Re: How to code excel to have cell change color when data is edited

Re: How to code excel to have cell change color when data is edited

Re: How to code excel to have cell change color when data is edited

Re: How to code excel to have cell change color when data is edited

Thread Information

Users Browsing this Thread

Similar Threads

Color highlight on change (Edited) cells

[SOLVED] Change cell color depending on cell color, error in code

[SOLVED] vba code to change the cell color by looking in a range that contains the color index valu

Code to Change Tab Color if Data Exists

Change Excel (2010) Row color per the value in the row-cell using VBA code

change cell color based on last edited date

[SOLVED] How do I change fill color in excel based on data in the cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions