Need VBA macro for Highlighting a Cell who's Value has Changed


Thanks Variatus.  I made a few adjustments to the previous sample file in order to get the "Last Updated" timestamp to work the way I need it to.  I took your advice and put a VBA on the actual data sheet, and then link that timstamp cell to the summary sheet.  Now the timestamp works fine, but since I am using a different VBA code than before, I need to know from this current point, what I need to do to get the Timestamp cells on the Summary sheet to highlight yellow when the linked data is changed.  Is there a conditional formatting formula that can be applied to highlight a cell if its current value is greater than it's previous value.  Since we're dealing with time, the change in these particular cells will always be greater than they were previously. Or is there a VBA code that can highlight a cell when it's value changes even though the change event in the cell actually occurs on another sheet.  Secondly, I'd like to use a Form Control Button to clear the highlight on that particular row and return the cell next to that button to no fill color. I'd like this to happen regardless of if the cell is highlighted automatically or manually.  Can you provide a Macro code for this as well.  In the new Sample Worksheet, we will only focus on the Termite Treatment row 15, and the timestamp cells that I want to highlight are in Column "O" on the Summary Sheet.  The Button to clear the corresponding timestamp highlight is in Column "P".  Any urgent help would be much appreciated.



If you could post a sample file so we can look at what code you are using would make helping you easier. To highlight the Timestamp cell would just mean tweaking your existing code. Creating a macro to remove the highlight will be easy after knowing more about where the highlight is. The third part to add and remove highlight of cells which have changed would need to know where the "proper range" is that you mention. If you can provide more detail you are more likely to get a meaningful solution.
WillieD24 (rep: 80) Sep 15, '20 at 8:04 pm
Karnold, this is a Q & A forum where many viewer come to look at what was asked and answered. It is not an Instagram blog where a dialog develops without record of what went before.
In future, please make sure that any modifications you make to your question don't change its original substance. When you have a new question, create a new thread. If you have two new questions, create two new threads. If one is building upon the otehr, wait for one question to be resolved before asking the next one.
Also, as a curtesy to others, remember to mark the answers that helped you while preserving their context. Thank you.
Variatus (rep: 4544) Sep 16, '20 at 10:10 pm
Understood, but I was unable to attach a new file after hitting the "Add to Discussion" button,  which is why I edited the original post.  Adding a new file to the discussion after the initial question should be an option. 
Karnold (rep: 2) Sep 16, '20 at 10:22 pm
Files can't be added to a discussion, only to questions and answers. If you want to add a file to the discussion, add it to the question. You can do that in edit mode.
Variatus (rep: 4544) Sep 17, '20 at 1:46 am
Add to Discussion



Applying the highlight just takes a minor amendment to the code you already have. Here it is.

'Determine if the input date/time should change
If myDateTimeRange.Value = "" Then
    With myDateTimeRange
        .Value = Now
        .Interior.Color = vbYellow
    End With
End If

Now, to undo the highlight you should use the same coding method you already have. But instead of capturing the Worksheet_Change event, you want the Worksheet_SelectionChange eventwhich occurs when a cell is clicked. Basically, set up the event procedure, copy the code from your change event procedure into it, and then modify the trigger range (MyTableRange) and the action as shown below.

If myDateTimeRange.Value = "" Then
    myDateTimeRange.Interior.Pattern = xlNone
End If

Answer the Question

You must create an account to use the forum. Create an Account or Login