+ Reply to Thread
Results 1 to 2 of 2

Change Cell Color if New Value Entered; Revert Color if Value Reverts to Original Value

Hybrid View

  1. #1
    Registered User
    Join Date
    11-03-2017
    Location
    USA
    MS-Off Ver
    2010
    Posts
    1

    Change Cell Color if New Value Entered; Revert Color if Value Reverts to Original Value

    Hello!

    I am trying to code a sheet so if:
    A) a user enters a different value in a cell than what is originally in the cell, the cell color changes to
    yellow.

    But....lets say the user made a mistake, accidentally changed a cell, and needs to change the cell back to it's original value. Here's the other condition I want coded:

    B) If the cell reverts back to it's original value, then the cell color should revert back to it's original
    color as well.

    The sheet I am trying to code this for is in a workbook that multiple users make inputs in. Each user has a column specific for them that they make entries for. The values in the range for user inputs [B:E] are either flagged as Yes (Y) or No (N). Existing Y cells have a gray fill. Exisiting N cells have no fill. See attached image.


    I've figured part A) out with the code below. Part B) is where I'm having trouble. Currently my code will change the cell color if a new value is entered, but if the user changes it back to the original value, the cell color remains yellow, because it is a another change in value.


    code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2:E13")) Is Nothing Then
    Target.Interior.ColorIndex = 6
    End If
    End Sub

    Appreciate any help on this!
    Attached Images Attached Images

  2. #2
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: Change Cell Color if New Value Entered; Revert Color if Value Reverts to Original Valu

    Hi @ rcw06002

    Changes made by a macro, are permanent, and do not have "Undo" option.
    So, to solve this problem, I use to make a hidden copy to compare.
    I've prepared a book with this trick.
    Every time you open the book, it makes a copy of the Data sheet,
    the background colouring is automatic,
    works the same with Ucase or Lcase .
    If different users are making changes, you should think in giving them passwords
    so they can change values in only their own column.
    Attached Files Attached Files
    Barriers are there for those who don't want to dream

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Change cell format (cell color+font color) based on color of another cell
    By Dedaluss in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-30-2017, 03:27 AM
  2. [SOLVED] Worksheet Change? Use a formula cell as input and then revert back to original formula...
    By trickyricky in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-01-2016, 11:39 PM
  3. Replies: 4
    Last Post: 05-17-2013, 10:37 AM
  4. Replies: 6
    Last Post: 05-08-2013, 07:56 AM
  5. Column chart - Change color/value based on original y value
    By leossj433 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-14-2012, 12:23 PM
  6. Change color of entire rows if a value is entered in a cell in column I
    By patrickmcdiver in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-23-2012, 08:04 AM
  7. Replies: 2
    Last Post: 08-09-2011, 09:54 AM

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