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.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Need VBA macro for Highlighting a Cell who's Value has Changed
Discussion
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.
Answers
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