+ Reply to Thread
Results 1 to 13 of 13

Change background color of cell when data has been changed

  1. #1
    Registered User
    Join Date
    07-10-2012
    Location
    Rushville Indiana
    MS-Off Ver
    Excel 2003
    Posts
    7

    Change background color of cell when data has been changed

    Of course I have an emergency. I have 10 excel sheets that are filled with data some 20,000 each.

    Some of the descriptions in that row need to be changed.

    I wonder how I can create some kind of macro or formatting that will change the text color or the fill color as I make those changes automatically?

    I can sort the list after that easy enough, pulling the color text or the fill color.

    Can someone please help!!

  2. #2
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: Change background color of cell when data has been changed

    Unfortunately, there's no sort by color or sort by fill. I'd recommend an additional column to flag changes that you'll then be able to sort on afterwards. As far as automatically noting changes, it would be possible with VBA, if you're allowed to use that on whatever spreadsheet you're working on. But it would be easier if I saw a sample spreadsheet with what areas may be altered.
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Change background color of cell when data has been changed

    Hi paperboy and welcome to the forum,

    Find the attached that does part of what you want. If you change any cell on the sheet it will change the backgrond color of that cell. See the VBA Event code behind sheet1 to see how it is done.

    You could improve the code by filling in an X in column C if it has changed. Then sort by col C as Miraun suggested above.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    07-10-2012
    Location
    Rushville Indiana
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Change background color of cell when data has been changed

    MarvinP,

    Thank you for the answer. I've copied your macro and added it to my sheet in Visiual Basic Editor and in Module and I can get those results. BUT, what your sheet is doing is EXACTLY WHAT I NEED. Please help.

  5. #5
    Registered User
    Join Date
    07-10-2012
    Location
    Rushville Indiana
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Change background color of cell when data has been changed

    Sorry I mean I can't...get those results. I am so close I know.

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Change background color of cell when data has been changed

    @ paperboy -
    what your sheet is doing is EXACTLY WHAT I NEED. Please help.
    Did you mean isn't instead of is?

    What did you need help on? Putting an X in a column so you can sort it later?

    Also - the VBA code should go behind the sheet and not in a module. Did you put it in both places?

  7. #7
    Registered User
    Join Date
    07-10-2012
    Location
    Rushville Indiana
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Change background color of cell when data has been changed

    You are perfect. After I put the code in the right spot it works like a charm. EXACTLY what I wanted, you have no idea how much appreciate this.

  8. #8
    Registered User
    Join Date
    08-10-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Change background color of cell when data has been changed

    Can you help me with Putting an X in a column so you can sort it later portion?

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Change background color of cell when data has been changed

    SandyLake, welcome to the forum

    Im not quite sure what you want, but if you are asking your own quesrion regarding paperboy's thread...
    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Registered User
    Join Date
    08-10-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Change background color of cell when data has been changed

    Sorry and thanks... I have resubmitted under my own post. Hopefully I'll get some help! I'm new as of today and will read the rules...

  11. #11
    Registered User
    Join Date
    09-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Change background color of cell when data has been changed

    Hi,

    This thread has been of great use to me and i have used your code, but i wondered if you could help me amend it slightly?

    I would like the colour change to happen to an amendend cell, but only if i has a different value to its original.

    For example, i am using a list function in excel and if someone changes the cell value and then decides the original option was better, i would like the colour to clear.

    I hope this makes sense, and that i have asked my question in line with the rules of this forum (as it is an extension to a code discussed in this thread). Apologies if not.

    Susan

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Change background color of cell when data has been changed

    susanhorn,

    Please check post 9.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  13. #13
    Registered User
    Join Date
    09-11-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Change background color of cell when data has been changed

    Many thanks. I shall take a look.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1