Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Indicate when data in a cell is changed (Excel 2000)

    I'm using the code below to color a cell if the data changes. It works good.
    The problem is I have an option group in a column on the sheet and id doesn't
    change the background when you select a different option. The cell that is linked
    works because I see the numbers change. Is there something else I need to make it work for the option buttons.
    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2", "M154")) Is Nothing Then
    With Target.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    End If
    End Sub</pre>



    Scott

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Indicate when data in a cell is changed (Excel 2000)

    The Worksheet_Change event only occurs when the user changes the value of a cell directly, not when the value changes as the result of something else such as a calculation, a macro, or because it is linked to a control. You could assign a macro to each of the option buttons - not a very attractive idea... Do you really need the cells to change color when the user clicks an option button?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indicate when data in a cell is changed (Excel 2000)

    Hans,
    Yes I do. Unless you can think of another way to show that a value has been changed.
    The working sheet will have around 150 rows.
    I want an easy way to let the sender of the sheet know when a user makes a change and sends it back.
    There may only be a couple of changes on the sheet and it would be hard to compare the changed sheet with the original sent.
    If I change an option button and try to close, I'm asked if I want to save the changes so something is seeing that I changed data.
    Is there a way to use the Calculate event?

    Scott


    Scott.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Indicate when data in a cell is changed (Excel 2000)

    The Excel application itself keeps track of a lot more than is available to the programmer through events.
    The Worksheet_Calculate event doesn't have a Target argument, so you don't know which cells were affected.
    A disadvantage of code is that a cell will remain colored even if the user undoes changes and restores the original value.

    Perhaps you can adapt the attached version to your needs. I copied the linked cells (C2:C11) to the clipboard, then used Paste Special > Values to place their current values in G2:G11.
    I set conditional formatting on C2:C11 to color the background if the value of a cell is not equal to its counterpart in G2:G11.
    Finally, I hid column G.
    If the user clicks an option button, the cell underneath will be colored. If the user restores the original situation, the coloring will disappear.
    No code needed, so the user will not get a macro warning.
    A disadvantage of this method is that you must prepare the worksheet before sending it out.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indicate when data in a cell is changed (Excel 2000)

    Hans,
    Thanks. That helped me finish the sheet.
    Works great.
    Scott

Posting Permissions

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